查询出多个班级内最大、最小、第二大的同学的信息，一条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

因为是多个班级，所以显示每个班级，最大的分数，最小的分数，第二大的分数