注: 本题提供了考虑并列以及不考虑并列的两种解法
解法(不考虑并列的情况)
该解法是不考虑并列的情况下
注意:此解法的前提是忽略成绩并列情况,否则出来的结果有问题。
先给结果sql,再讲思路:
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
LEFT JOIN score b ON a.c_id = b.c_id
AND a.s_score <= b.s_score
GROUP BY
a.s_id,
a.c_id,
a.s_score
HAVING
COUNT( b.s_id ) <= 3
ORDER BY
a.c_id,
a.s_score DESC
思路:
1.首先我们可以看下表结构,如下
- 两表进行关联后的结果:
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
LEFT JOIN score b ON a.c_id = b.c_id
加入a.s_score<b.s_score然后接下来这个继续分析这个语句:
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score < b.s_score
也就是我们现在是a表的成绩,要小于或者等于关联的b表的成绩,我们拿a表一条数据来分析:
关联后的表为:
那么此时a.score = 90,执行a.score<=b.score ,则出来的结果只有1条:
我们拿课程1的第二名的记录来继续分析:
表关联后的结果为:
此时a.score = 80,则执行a.score<=b.score,过滤后的结果为2条记录,即
拿课程1的第三条记录来分析,则
关联后的结果为:
此时a.score = 70,执行a.score<=b.socre,则过滤后的结果为3条记录:
到这里我们可以知道,排名第一,过滤出一条,排名第二, 过滤出2条,排名第三,过滤出第三条,依次类推,排名第四,过滤4条,排名第五,过滤5条…
接着分组,这个按a表的学生id、课程id、成绩分组即可。
然后我们要的是前三名的记录,我们根据我们推出的结果,也就是count(a.s_id)<=3即可得出前三名。
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
LEFT JOIN score b ON a.c_id = b.c_id
AND a.s_score <= b.s_score
GROUP BY
a.s_id,
a.c_id,
a.s_score
HAVING
COUNT( b.s_id ) <= 3
最后再按课程号进行排序,再按成绩进行降序显示即可
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
LEFT JOIN score b ON a.c_id = b.c_id
AND a.s_score <= b.s_score
GROUP BY
a.s_id,
a.c_id,
a.s_score
HAVING
COUNT( b.s_id ) <= 3
ORDER BY
a.c_id,
a.s_score DESC
解法2(考虑并列的情况)
该解法是考虑并列的情况下
考虑并列的解法:
SELECT
s1.*
FROM
score s1
WHERE
(
SELECT
count(1)
FROM
score s2
WHERE
s2.c_id = s1.c_id
AND s2.s_score > s1.s_score
) < 3
ORDER BY
s1.c_id,
s1.s_id
并列情况下的结果集举例: