mysql复杂场景的查询

最新推荐文章于 2024-05-16 17:00:09 发布

华大哥

最新推荐文章于 2024-05-16 17:00:09 发布

阅读量318

点赞数

分类专栏：大数据文章标签： sql 数据库

本文链接：https://blog.csdn.net/lchmyhua88/article/details/105027009

版权

大数据专栏收录该内容

62 篇文章 0 订阅

订阅专栏

最近没事干，突然想到之前一个问题: mysql查询复杂sql,比如我现在的场景是多个年级每个班级前三名的排序

CREATE TABLE `student` (
`id` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`create_time` datetime(6) DEFAULT NULL,
`uuid` varchar(100) CHARACTER SET latin1 DEFAULT '',
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入数据：

insert into `student` (`id`, `create_time`, `uuid`, `score`) values('1','2020-03-04 13:45:50.000000','1class','82');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('2','2020-03-05 13:46:02.000000','1class','95');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('3','2020-03-11 13:45:58.000000','1class','82');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('4','2020-03-22 13:46:04.000000','1class','40');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('5','2020-03-05 13:46:15.000000','1class','20');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('6','2020-03-02 13:46:07.000000','2class','95');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('7','2020-03-25 13:46:12.000000','2class','40');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('8','2020-04-02 13:46:18.000000','2class','3');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('9','2020-03-12 13:46:23.000000','2class','60');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('10','2020-03-12 13:46:21.000000','2class','10');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('11','2020-02-26 13:46:29.000000','3class','70');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('12','2020-03-03 13:46:26.000000','3class','60');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('13','2020-02-25 13:46:32.000000','3class','40');
insert into `student` (`id`, `create_time`, `uuid`, `score`) values('14','2020-03-21 13:46:34.000000','3class','90');

这个时候我要查询每个班级前3名的分数，执行sql如下：

SELECT t.uuid
    , t.score
    , t.create_time
    , t.rn
     FROM (
SELECT *
FROM (
    SELECT l1.*
        , IF(@prev = l1.uuid, @rank := @rank + 1, @rank := 1) AS rn
        , @prev := l1.uuid
    FROM student l1
    JOIN ( SELECT @prev := NULL ) init
    ORDER BY l1.uuid
        , l1.score DESC
) l2
WHERE l2.rn <= 3 ) as t