-- 表结构
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