MySQL中求排名

MySQL中求排名

方法一、自增列（以前项目用过，速度快，但需要重写全表）

 

方法二、select @rank:=@rank+1 as rank (这个很好，来自国外网站）

http://www.fromdual.com/ranking-mysql-results

Lets do first some preparation for the example:

 CREATE TABLE sales (      id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY    , fruit  VARCHAR(32)    , amount DECIMAL  );

 INSERT INTO sales  VALUES (NULL, 'apple', 12.75), (NULL, 'orange', 1.89), (NULL, 'pear', 19.23)       , (NULL, 'banana', 4.25), (NULL, 'cherry', 123.75), (NULL, 'plum', 23.15)  ;Now lets query:

 SET @rank=0;

 SELECT @rank:=@rank+1 AS rank, fruit, amount    FROM sales   ORDER BY amount DESC  ;方法三、这是国内网上常见的方式，效率不高，有子查询

这个是占位排序的写法，即出现分数相同时，后面的名次会空出，即1，2，2，4，4，6这样的排名

select * from ( SELECT user_id,scoring , (SELECT COUNT(scoring ) FROM t_user WHERE scoring >a.scoring)+1 place FROM t_user a ORDER BY user_id,place )x order by scoring desc还有不占位排名，即1，2，2，3，3，4这样的排名，

select * from ( select user_id,scoring , (select count(distinct scoring ) from t_user where scoring >a.scoring)+1 place from t_user a order by user_id,place )x order by scoring desc