MySQL 使用 GROUP BY 对结果进行排名 [英] MySQL ranked results with GROUP BY

问题描述

我有这张桌子:

id leaderboard_id player_id score
 1              1        3 5001
 2              1        2  501
 3              1        4  490
 4              2        3 1001
 5              2        2  110

我想在两个排行榜上都获得 player_id = 3 的排名(leaderboard_id = 1 和 2).

I want to get the player_id = 3 rank on both leaderboards (leaderboard_id = 1 and 2).

我尝试了很多选择都没有成功，他们给了我在排行榜 1 中的排名 1，在排行榜 2 中的排名 2，而他们都应该是排名 1.

I tried many options with no success, they give me rank 1 in leaderboard 1, rank 2 in leaderboard 2, when they both should be rank 1.

给我这些结果的最后一个代码是:

Last code that gave me those results is:

SELECT * FROM ( SELECT s.*, @rank := @rank + 1 rank FROM ( SELECT leaderboard_id, player_id, score FROM leaderboards t GROUP BY leaderboard_id ) s, (SELECT @rank := 0) init ORDER BY score DESC ) r WHERE player_id = 3

...结果如下:

如果有人能指出解决方案，将不胜感激.

If anyone can point to a solution, it would be very appreciated.

谢谢

推荐答案

在 MySql 8 中你可以使用 窗口函数 像 ROW_NUMBER 或DENSE_RANK 为此.

In MySql 8 you could use window functions like ROW_NUMBER or DENSE_RANK for this.

在 MySql 7 中，您可以使用变量模拟这些窗口函数.

In MySql 7 you can emulate those window functions using variables.

在您的情况下，您希望每个排行榜都有一个排名.
所以它需要检查排行榜是否改变.
子查询中的 ORDER BY 对该计算很重要.

In your case you would want a ranking per leaderboard.
So it needs to check if the leaderboard changed.
The ORDER BY in the sub-query does matter for that calculation.

示例数据:

-- Test Table
drop table if exists test_leaderboards;
create table test_leaderboards (
  id int primary key auto_increment,
  leaderboard_id int not null,
  player_id int not null,
  score int not null
);

-- Sample Data
insert into test_leaderboards (leaderboard_id, player_id, score) values
(1, 3, 3333),
(1, 2, 2222),
(1, 4, 4444),
(2, 3, 3333),
(2, 2, 2222),
(2, 1, 1111);

查询:

SELECT leaderboard_id, player_id, score, rank
FROM 
( 
  SELECT leaderboard_id, player_id, score, 
    CASE 
    WHEN leaderboard_id = @prev then @rank := @rank + 1
    -- Remark: a variable assignement is always true
    WHEN @prev := leaderboard_id then @rank := 1
    END AS rank 
  FROM 
  (
       SELECT leaderboard_id, player_id, score
       FROM test_leaderboards
       ORDER BY leaderboard_id ASC, score DESC
  ) data
  CROSS JOIN (SELECT @rank := null, @prev := null) AS init
) r 
WHERE player_id = 3;

结果:

leaderboard_id  player_id   score   rank
--------------  ---------   -----   ----
1               3           3333    2
2               3           3333    1

这篇关于MySQL 使用 GROUP BY 对结果进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！