leetcode -- sql -- 获取各课程第二高成绩的人的信息

-- 表结构

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `student_id` int(11) NOT NULL COMMENT '学生id',
  `class_id` int(11) NOT NULL COMMENT '学科id',
  `score` int(11) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

 

-- 第一步 ： 获得各个部门成绩最高的数值

SELECT MAX(score) score , class_id
                                from student
                                GROUP BY class_id

-- 第二部 ：将查出来的子表和原来的表关联查询,查询出每科目最大分数的人

SELECT b.*
FROM (
                SELECT MAX(score) score , class_id
                        from student
                        GROUP BY class_id
            )a
LEFT JOIN student b on a.class_id = b.class_id and a.score = b.score

-- 第三步 ：获得各个部门第二高的成绩的人，就是将第二部获得的人排除掉，然后获得最大

SELECT MAX(score) score , class_id
from student
where id not in (
                                    SELECT b.id
                                    FROM (
                                        SELECT MAX(score) score , class_id
                                                from student
                                                GROUP BY class_id
                                    )a
                                    LEFT JOIN student b on a.class_id = b.class_id and a.score = b.score
                                )
GROUP BY class_id

 

-- 第四部 ：根据第二大的成绩和课程id获得所有信息

 

SELECT b.*
FROM (
                SELECT MAX(score) score , class_id
                from student
                where id not in (
                                                    SELECT b.id
                                                    FROM (
                                                        SELECT MAX(score) score , class_id
                                                                from student
                                                                GROUP BY class_id
                                                    )a
                                                    LEFT JOIN student b on a.class_id = b.class_id and a.score = b.score
                                                )
                GROUP BY class_id
            )a
LEFT JOIN student b on a.class_id = b.class_id and a.score = b.score