mysql 实现查询排行榜

总结下mysql的排行榜查询，mysql 8 可以使用窗口函数，8以前就不行了。需求大概是一个游戏，用户可以玩多次，排名的时候取最高分排名

首先搞点测试数据

CREATE TABLE `t_game` (
    `id`          BIGINT(20) NOT NULL AUTO_INCREMENT,
    `user_id`     BIGINT(20) ,
    `score`       INT(11)    ,
    `create_date` DATE       ,
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;

INSERT INTO `t_game`(`id`, `user_id`, `score`, `create_date`)
VALUES (1, 1, 19, '2019-11-28'),
       (2, 2, 96, '2019-02-09'),
       (3, 3, 65, '2019-12-07'),
       (4, 4, 75, '2019-09-29'),
       (5, 5, 60, '2019-10-11'),
       (6, 6, 8, '2019-02-03'),
       (7, 7, 20, '2019-10-06'),
       (8, 8, 19, '2019-09-05'),
       (9, 9, 81, '2019-01-14'),
       (10, 10, 75, '2019-08-26'),
       (11, 1, 97, '2019-01-20'),
       (12, 2, 97, '2019-02-27'),
       (13, 3, 0, '2019-07-19'),
       (14, 4, 73, '2019-01-06'),
       (15, 5, 88, '2019-05-11'),
       (16, 6, 15, '2019-09-16'),
       (17, 7, 7, '2019-03-26'),
       (18, 8, 95, '2019-01-21'),
       (19, 9, 87, '2019-12-30'),
       (20, 10, 68, '2019-06-11');

CREATE TABLE `t_user` (
    `id`       BIGINT(20) NOT NULL AUTO_INCREMENT,
    `nickname` VARCHAR(50) ,
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;

INSERT INTO `t_user`(`id`, `nickname`)
VALUES (1, '823E2A6B'),
       (2, '8248806C'),
       (3, '8248821D'),
       (4, '824882FB'),
       (5, '82488337'),
       (6, '82488373'),
       (7, '824883B6'),
       (8, '824883E9'),
       (9, '8248844F'),
       (10, '82488488');

常见的排名分两种，一种是分数相同就按照提交分数的时间排名，另一种是分数相同排名就一样。

由于GROUP BY
后的出的分数，不能确定是不是我们想要的最高分的那条记录，所以排名时有分两种情况。一种只看要排名对了就行，另外一种是查询出来的排名信息的每条记录上的所有字段都是正确的。

只保证关键数据正确

不使用开窗函数

SELECT t.*,
       IF(@lastMaxScore = maxScore, @no, @no := @tempNo) no,
       @tempNo := @tempNo + 1                            tempNo,
       @lastMaxScore := maxScore                         lastMaxScore
FROM (
         SELECT *, max(score) maxScore
         FROM t_game
         GROUP BY user_id
         ORDER BY maxScore DESC, id
     ) t,
     (SELECT @no := 1, @lastMaxScore := 0, @tempNo := 1) rt;

使用开窗函数

SELECT *,
       max(score)                              maxScore,
       rank() OVER (ORDER BY max(score) DESC ) no
FROM t_game
GROUP BY user_id

查询结果

+----+---------+-------+-------------+----------+------+
| id | user_id | score | create_date | maxScore | no   |
+----+---------+-------+-------------+----------+------+
|  1 |       1 |    19 | 2019-11-28  |       97 | 1    |
|  2 |       2 |    96 | 2019-02-09  |       97 | 1    |
|  8 |       8 |    19 | 2019-09-05  |       95 | 3    |
|  5 |       5 |    60 | 2019-10-11  |       88 | 4    |
|  9 |       9 |    81 | 2019-01-14  |       87 | 5    |
|  4 |       4 |    75 | 2019-09-29  |       75 | 6    |
| 10 |      10 |    75 | 2019-08-26  |       75 | 6    |
|  3 |       3 |    65 | 2019-12-07  |       65 | 8    |
|  7 |       7 |    20 | 2019-10-06  |       20 | 9    |
|  6 |       6 |     8 | 2019-02-03  |       15 | 10   |
+----+---------+-------+-------------+----------+------+

不使用开窗函数
```
 
```

SELECT t.*, @no := @no + 1 no
 FROM (
          SELECT *, max(score) maxScore
          FROM t_game
          GROUP BY user_id
          ORDER BY maxScore DESC, id
      ) t,
      (SELECT @no := 0) rt;

使用开窗函数

SELECT *,
       max(score)                                    maxScore,
       row_number() OVER (ORDER BY max(score) DESC ) no
FROM t_game
GROUP BY user_id

查询结果

+----+---------+-------+-------------+----------+------+
| id | user_id | score | create_date | maxScore | no   |
+----+---------+-------+-------------+----------+------+
|  1 |       1 |    19 | 2019-11-28  |       97 |    1 |
|  2 |       2 |    96 | 2019-02-09  |       97 |    2 |
|  8 |       8 |    19 | 2019-09-05  |       95 |    3 |
|  5 |       5 |    60 | 2019-10-11  |       88 |    4 |
|  9 |       9 |    81 | 2019-01-14  |       87 |    5 |
|  4 |       4 |    75 | 2019-09-29  |       75 |    6 |
| 10 |      10 |    75 | 2019-08-26  |       75 |    7 |
|  3 |       3 |    65 | 2019-12-07  |       65 |    8 |
|  7 |       7 |    20 | 2019-10-06  |       20 |    9 |
|  6 |       6 |     8 | 2019-02-03  |       15 |   10 |
+----+---------+-------+-------------+----------+------+

排名不重复
排名可重复

保证所有数据正确

不使用开窗函数

使用开窗函数

查询结果

不使用开窗函数

使用开窗函数

查询结果

排名不重复

SELECT id, user_id, score, create_date, row_number() OVER (ORDER BY score DESC) no
FROM (
         SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY score DESC) i
         FROM t_game
     ) t
WHERE i = 1

+----+---------+-------+-------------+----+
| 11 |       1 |    97 | 2019-01-20  |  1 |
| 12 |       2 |    97 | 2019-02-27  |  2 |
| 18 |       8 |    95 | 2019-01-21  |  3 |
| 15 |       5 |    88 | 2019-05-11  |  4 |
| 19 |       9 |    87 | 2019-12-30  |  5 |
|  4 |       4 |    75 | 2019-09-29  |  6 |
| 10 |      10 |    75 | 2019-08-26  |  7 |
|  3 |       3 |    65 | 2019-12-07  |  8 |
|  7 |       7 |    20 | 2019-10-06  |  9 |
| 16 |       6 |    15 | 2019-09-16  | 10 |
+----+---------+-------+-------------+----+

SELECT id, user_id, score, create_date, @no := @no + 1 no
FROM (
         SELECT *, IF(@tmp <> user_id, @i := 1, @i := @i + 1) i, @tmp := user_id
         FROM (
                  SELECT *
                  FROM t_game
                  ORDER BY user_id, score DESC, id
              ) t,
              (SELECT @i := 0, @tmp := NULL) it
     ) t1,
     (SELECT @no := 0) rt
WHERE i = 1
ORDER BY score DESC, t1.id

排名可重复

SELECT id,
       user_id,
       score,
       create_date,
       rank() OVER (ORDER BY score DESC) no
FROM (
         SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY score DESC) i
         FROM t_game
     ) t
WHERE i = 1

+----+---------+-------+-------------+----+
| id | user_id | score | create_date | no |
+----+---------+-------+-------------+----+
| 11 |       1 |    97 | 2019-01-20  |  1 |
| 12 |       2 |    97 | 2019-02-27  |  1 |
| 18 |       8 |    95 | 2019-01-21  |  3 |
| 15 |       5 |    88 | 2019-05-11  |  4 |
| 19 |       9 |    87 | 2019-12-30  |  5 |
|  4 |       4 |    75 | 2019-09-29  |  6 |
| 10 |      10 |    75 | 2019-08-26  |  6 |
|  3 |       3 |    65 | 2019-12-07  |  8 |
|  7 |       7 |    20 | 2019-10-06  |  9 |
| 16 |       6 |    15 | 2019-09-16  | 10 |
+----+---------+-------+-------------+----+

SELECT id,
       user_id,
       score,
       create_date,
       IF(@lastScore = score, @no, @no := @tempNo) no,
       @tempNo := @tempNo + 1                      tempNo,
       @lastScore := score                         lastScore
FROM (
         SELECT *, IF(@tmp <> user_id, @i := 1, @i := @i + 1) i, @tmp := user_id
         FROM (
                  SELECT *
                  FROM t_game
                  ORDER BY user_id, score DESC, id
              ) t,
              (SELECT @i := 0, @tmp := NULL) it
     ) t1,
     (SELECT @no := 1, @lastScore := 0, @tempNo := 1) rt
WHERE i = 1
ORDER BY score DESC, t1.id;

mysql 实现查询排行榜

评论