代码之家 › 专栏 › 技术社区 › SteveEdson

MySQL:如何获得一系列活动的排行榜位置

sql mysql

SteveEdson · 技术社区 · 6 年前

我有以下数据结构(简化):

Users:
    ID
    Name

Events:
    ID
    Date

Results:
    ID
    User ID (foreign key)
    Event ID (foreign key)
    Points: (int)

我想知道(最好是最有效的方法):

与其他用户相比,如何获得用户在“联盟”中的位置。如果可能,使用一个查询(或子查询),如何按事件细分,例如,第一个事件、第二个事件、第三个事件之后的用户位置等。

我可以通过以下方式获得排行榜:

select users.name, SUM(results.points) as points
from results
inner join users on results.user_id = users.id
group by users.id
order by points DESC

但是,如果可能的话,我想知道用户的位置,而不必返回整个表。

编辑:我提供了一些示例数据 here .

理想输出:

| User ID | Rank  |
| 3       | 1     |
| 1       | 2     |
| 2       | 3     |

和类似的东西(不完全像这样,它是灵活的,只是显示用户在每个事件中的排名)

| User ID | After Event | Rank  |
| 1       | 1           | 1     |
| 1       | 2           | 1     |
| 1       | 3           | 2     |
| 2       | 1           | 2     |
| 2       | 2           | 2     |
| 2       | 3           | 1     |
| 3       | 1           | 3     |
| 3       | 2           | 3     |
| 3       | 3           | 3     |

2 回复 | 直到 6 年前

Kamil Gosciminski 6 年前

mysql 8.0+支持 window functions 所以使用 dense_rank() 很方便。

8.0下的MySQL解决方案

由于您的版本是5.7,因此您可以如下模拟:

select 
  t.id,
  CASE WHEN @prevRank = points THEN @currRank
       WHEN @prevRank := points THEN @currRank := @currRank + 1
   END AS rank
from (
  select users.id, SUM(results.points) as points
  from results
  inner join users on results.user_id = users.id
  group by users.id
  order by points DESC
  ) t
  cross join (SELECT @currRank := 0, @prevRank := NULL) r

如果您需要特定的数据 user 然后添加 WHERE 筛选外部查询中其他所有人的条件:

select *
from (
<< above query here >>
) t
where id = ? -- your id here

mysql 8.0+解决方案

rank 是保留关键字,因此在命名列时需要反勾号。我们正在使用 dense_rank 窗口函数,它将根据获取的点的降序排序来分配等级:

select id, dense_rank() over (order by points desc) as `rank`
from (
  select users.id, SUM(results.points) as points
  from results
  inner join users on results.user_id = users.id
  group by users.id
  ) t
order by `rank`

Zaynul Abadin Tuhin 6 年前

SET @rowno = 0;
select UserID, max(points), @rowno:=@rowno+1 as rank  from 
(
select users.id as UserID ,users.name as users_name,events.name, SUM(results.points) as points
from results
inner join users on results.user_id = users.id
inner join events on results.event_id= events.id
group by users.id,events.name,users.name
order by points DESC
) as T
group by UserID

order by max(points) desc