菜单

MySQL分数排名

2011年07月6日 - mysql

方法一、自增列(速度快,但需要重写全表)

方法二、select @rank:=@rank+1 as rank 

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

Now lets query:

SELECT fruit, amount FROM sales ORDER BY amount DESC;

And now with ranking:

SET @rank=0; select a.* from (SELECT @rank:=@rank+1 AS rank, uid, name, feng FROM fengshu ORDER BY feng DESC) as a order by a.name;

方法三、这是国内网上常见的方式,效率不高,有子查询
这个是占位排序的写法,即出现分数相同时,后面的名次会空出,即1,2,2,4,4,6这样的排名

select * from (
SELECT uid,name , feng,
(SELECT COUNT(feng ) FROM fengshu WHERE feng >a.feng)+1 rank
FROM fengshu a
ORDER BY rank
)x order by name 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

MySQL分数排名》有1个想法

xy

建议修改theme,体验很不好。

回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注