查询每年成绩前三名的学生

小咪咪 2022-02-21 08:19 191阅读 0赞

创建表：

CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `year` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `score` int(255) DEFAULT NULL,
      `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入数据：

INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (1, '2016', 90, '张三');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (2, '2016', 80, '李四');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (3, '2016', 70, '王五');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (4, '2016', 60, '赵六');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (5, '2017', 90, '张三');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (6, '2017', 80, '李四');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (7, '2017', 70, '王五');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (8, '2017', 60, '赵六');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (9, '2018', 90, '张三');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (10, '2018', 80, '李四');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (11, '2018', 70, '王五');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (12, '2018', 60, '赵六');
    INSERT INTO `test`(`id`, `year`, `score`, `name`) VALUES (13, '2019', 1, '哈哈');

表如下：  
![在这里插入图片描述][20190410165824268.png]  
查询sql语句：

select * from test t where (select count(*) from test where year = t.year and score > t.score ) <3 order by t.year desc,t.score desc;

查询结果：  
![在这里插入图片描述][20190410165928730.png]

下面这两条sql也能查出来同样结果：

select a1.* from test a1 inner join (select a.year,a.score from test a left join test b on a.year = b.year and a.score<=b.score group by a.year,a.score having count(a.score)<=3) b1 on a1.year = b1.year and a1.score = b1.score order by a1.year desc,a1.score desc;
    
    select * from test t where (select count(1) + 1 from test where year = t.year and score > t.score) <4 order by t.year desc,t.score desc;

[20190410165824268.png]: /images/20220221/19bb4ba1609d46b68c741e7084a2bd5d.png
[20190410165928730.png]: /images/20220221/d582d88c4cd04278863651d08c9faab5.png