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