MySQL使用GROUP BY对结果进行排名

Question

我有一张桌子：

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

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

我尝试了许多选项，但都没有成功，它们给我在排行榜1中的排名1，在排行榜2中的排名2，而它们都应该是排名1。

给我这些结果的最后一段代码是：

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

..。结果如下：

​

​

如果有人能指出一个解决方案，我们将不胜感激。

谢谢

Answer 1

在MySql 8中，你可以使用像ROW_NUMBER或DENSE_RANK这样的window functions。

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

在您的案例中，您需要每个排行榜的排名。

所以它需要检查排行榜是否发生了变化。

子查询中的ORDER BY对该计算很重要。

示例数据：

-- 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