mysql 查排行榜_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.*, @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 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 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 |

+----+---------+-------+-------------+----+

排名可重复

不使用开窗函数

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 |

+----+---------+-------+-------------+----+

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值