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

转载于:https://www.cnblogs.com/kakasea/archive/2012/12/26/2834200.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值