欢迎光临
我们一直在努力

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后的出的分数,不能确定是不是我们想要的最高分的那条记录,所以排名时有分两种情况。一种只看要排名对了就行,另外一种是查询出来的排名信息的每条记录上的所有字段都是正确的。

  1. 只保证关键数据正确
    1. 排名不重复
      • 不使用开窗函数
         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 |
        +----+---------+-------+-------------+----------+------+
        
    2. 排名可重复
      • 不使用开窗函数
        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   |
        +----+---------+-------+-------------+----------+------+
        
  2. 保证所有数据正确
    1. 排名不重复
      • 不使用开窗函数
      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, 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 |
      +----+---------+-------+-------------+----+
      
    2. 排名可重复
      • 不使用开窗函数
      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;
      
      • 使用开窗函数
      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 |
      +----+---------+-------+-------------+----+

 

打赏
未经允许不得转载: » mysql 查询排行榜
分享到: 更多 (0)

评论 0

莫等闲、白了少年头,空悲切 - 繁华emoji

联系我们 联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏