当前位置 博文首页 > ChinaManor的博客：Mysql强化练习-难题之查询所有课程的成绩第2
– 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT t1.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 1,2) t1
UNION ALL
SELECT t2.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="02"
ORDER BY sc.s_score DESC LIMIT 1,2) t2
UNION ALL
SELECT t3.* FROM
(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="03"
ORDER BY sc.s_score DESC LIMIT 1,2) t3
这是一道mysql难题,也是经典题,搞懂这题就懂得一类题了.
逐条解析
1.从学生表当中选择
SELECT t1.* FROM (student)
2.将学生表和分数表连接从中选择学生信息,课程id,课程名字,分数
SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
3.将学生表和课程表连接,倒序
,抽取第2,第3名
,且课程id为 01的课程信息
并按分数归类
INNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="01"
ORDER BY sc.s_score DESC LIMIT 1,2
猜你喜欢:
– 25、查询各科成绩前三名的记录
– 42、查询每门功成绩最好的前两名
cs