查询排行及所有(表名.*)

1.

set @rownum=0;SELECT @rownum:=@rownum+1 AS top, customer.*FROM  customer

 

2.

SELECT @rownum:=@rownum+1 AS top, customer.* FROM (SELECT @rownum:=0) r, customer ORDER BY customer.this_month_cost DESC

1和2的结果：

查询排行及id(表名.id)

SELECT @rownum:=@rownum+1 AS top, customer.idFROM (SELECT @rownum:=0) r, customer ORDER BY customer.this_month_cost DESC

结果：

排序后更新原表的排行：

UPDATE `customer` c, (    SELECT        @rownum :=@rownum + 1 AS rownum,        customer.id    FROM        (SELECT @rownum := 0) r,        customer    ORDER BY        customer.this_month_cost DESC) aSET c.top = a.rownumWHERE    c.id = a.id