查询出多个班级内最大、最小、第二大的同学的信息，一条sql完成

本文主要是介绍查询出多个班级内最大、最小、第二大的同学的信息，一条sql完成，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

创建表

CREATE TABLE `biao` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`banji` varchar(10) DEFAULT NULL COMMENT '班级',
`score` int DEFAULT NULL COMMENT '成绩',
`name` varchar(10) DEFAULT NULL COMMENT '学生姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据

INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (1, '一年级', 60, '张三');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (4, '一年级', 90, '赵六');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (7, '一年级', 20, '李逵');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (10, '一年级', 30, '李白');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (13, '一年级', 50, '富贵');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (16, '一年级', 60, '小九');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (19, '一年级', 66, '武松');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (3, '三年级', 80, '王五');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (6, '三年级', 30, '小红');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (9, '三年级', 100, '王维');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (12, '三年级', 80, '王麻子');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (15, '三年级', 55, '霍三娘');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (18, '三年级', 22, '花荣');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (21, '三年级', 80, '镇关西');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (2, '二年级', 70, '李四');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (5, '二年级', 40, '王小明');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (8, '二年级', 10, '宋江');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (11, '二年级', 70, '张居正');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (14, '二年级', 70, '张顺');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (17, '二年级', 33, '田七');
INSERT INTO `dmg1`.`biao` (`id`, `banji`, `score`, `name`) VALUES (20, '二年级', 77, '武大郎');

sql讲解

1.因为是多个班级，那么我们肯定是要按照班级去进行分组

2.先把最大和最小的查询出来

3.然后再使用窗口函数，dense_rank,这个函数的意思就是就是对分数进行排名

4.不会跳过排名，比如说相同的分数都是 2 2 2，这样的排名，那么第三名还是显示3

5.对分数进行倒叙排序，然后再对班级进行分区，就和group by一样的意思

6.这样就能查询所有班级的分数的排名

7.然后在外层只查询分数，因为会存在相同的排名，所以要去重复

8.然后只查询排名是2的，也就是第二大的排名，并且班级要和最上面的班级一样

9.这样才能显示每个班级的第二大的分数

实现查询语句

SELECT
b.banji,
max(b.score) as 最大,
min(b.score) as 最小,
(

SELECT DISTINCT score from(
       SELECT
           id,
           banji,
           NAME,
           score,
           DENSE_RANK() over (PARTITION by banji ORDER BY score DESC ) AS paiming
           FROM biao
   )as t
   WHERE paiming=2   and b.banji=banji

)as 第二大

from biao as b
GROUP BY b.banji