MySQL:如何为系列中的每个事件获得排行榜位置

Question

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

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     |

Answer 1

MySQL 8.0+ 支持 window functions所以使用 dense_rank() 就派上用场了。

---

MySQL 8.0下解决方案

因为你的版本是 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`