1. 学生表(sid,sname)
分数表(主键id,学生id(sid),分数(score),科目id(cid))
科目表(cid,cname)
①查询每科的前三名
SELECT
student.s_no,
student.s_name,
course.c_name,
grade_temp.score
FROM
(
SELECT
*
FROM
(
SELECT
t1.*, (
SELECT
count(*)
FROM
grade t2
WHERE
t1.score <= t2.score
AND t1.c_id = t2.c_id
) AS rownum
FROM
grade t1
) t3
WHERE
rownum = 3
ORDER BY
c_id,
score DESC
) grade_temp
LEFT JOIN course ON grade_temp.c_id = course.c_id
LEFT JOIN student ON grade_temp.s_id = student.s_id