SQL用户通过分组得分排名
SQL用户通过分组得分排名
提问于 2015-08-23 11:40:53
我有下面的排名系统。
SET @1=0;
SELECT id, username, magic_xp, @i:=@i+1 AS rank
FROM hs_users
ORDER
BY magic_xp DESC;
hs_users
id username magic_xp rank
988 5hapescape 14926854 1
737 Ozan 13034431 2
989 Kurt 13034431 3
6 LEGACY 0 4
11 Bobby 0 5
276 Bobby123 0 6
345 Mynamesjason 0 7
450 Demon Spawn 0 8
987 Satan 0 9正如您所看到的，我有两个用户拥有相同的xp。
我想让他们两个都有rank = 2，剩下的应该是3。
我怎么能这样把他们分组？
| username | magic_xp | rank |
| ---------- + -------- + ---- |
| ShapeScape | 1000 | 1 |
| Kurt | 100 | 2 |
| Ozan | 100 | 2 |
| Legacy | 10 | 3 |回答 4
Stack Overflow用户
回答已采纳
发布于 2015-08-23 11:46:22
查询
set @i := 0;
set @lagxp := null;
select id, username, magic_xp,
@i := if(@lagxp = magic_xp, @i,
if(@lagxp := magic_xp, @i + 1, @i + 1)) as rank
from hs_users
order by magic_xp desc
;或
SELECT id, username, magic_xp,
IF (@score=hs_users.magic_xp, @rank:=@rank, @rank:=@rank+1) as rank,
@score:=hs_users.magic_xp score
FROM hs_users, (SELECT @score:=0, @rank:=0) r
ORDER BY magic_xp DESC;输出
+-----+------------+----------+------+----------+
| id | username | magic_xp | rank | lagxp |
+-----+------------+----------+------+----------+
| 988 | Shapescape | 14926894 | 1 | 14926894 |
| 737 | Ozan | 13034431 | 2 | 13034431 |
| 989 | Kurt | 13034431 | 2 | 13034431 |
| 6 | Legacy | 0 | 3 | 0 |
+-----+------------+----------+------+----------+木琴
Stack Overflow用户
发布于 2015-08-23 11:47:12
在MySQL中，最有效的方法是使用变量：
select t.*,
(@rank := if(@magic_xp = magic_xp, @rank,
if(@magic_xp := magic_xp, @rank + 1, @rank + 1)
)
) as rank
from table t cross join
(select @rank := 0, @magic_xp := NULL) params
order by magic_xp desc;注意变量的复杂表达式。两个变量的赋值都在一个表达式中。这是故意的。MySQL不保证SELECT中表达式的赋值顺序，有时甚至不按顺序计算它们。一个表达式是实现这一逻辑的安全方法。
SQL中一种更标准的方法是使用关联子查询：
select t.*,
(select count(distinct t2.magic_xp)
from table t2
where t2.magic_xp >= t.magic_xp
) as rank
from table t;Stack Overflow用户
发布于 2015-08-23 11:48:11
提出解决方案:)
SELECT id, username, magic_xp,
IF (@score=hs_users.magic_xp, @rank:=@rank, @rank:=@rank+1) as rank,
@score:=hs_users.magic_xp score
FROM hs_users, (SELECT @score:=0, @rank:=0) r
ORDER BY magic_xp DESC;感谢@amdixon
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32166261
复制相关文章
相似问题
腾讯云开发者
