MySQL 查询各科前三的数据
方法一：
SELECT sc.*,c.`
Cname` ,COUNT(sc.C)
FROM sc
LEFT JOIN sc a
ON sc.C = a.C AND sc.score >= a.score
LEFT JOIN course c
ON sc.C = c.C
WHERE sc.C = c.C
GROUP BY
sc.C,sc.S,sc.score
HAVING COUNT(sc.C)>=4
ORDER BY a.C,a.score DESC
解析：
先copy 一个sc 表中的数据，与原sc 进行对比。统计每个score 大于其他数据的计数。再按sc.C,sc.S进行分组
SELECT sc.* ,COUNT(sc.C) FROM sc LEFT JOIN sc a ON sc.C = a.C AND sc.score >= a.score GROUP BY sc.C,sc.S
可以看到，第一行数据的count计数为 6，由于我们要取每科前三名，sc表中共有student S编号7个，
自身不纳入count进行计数，则为6个需要取出前3--计数大于3的值。
SELECT sc.* ,COUNT(sc.C) FROM sc LEFT JOIN sc a ON sc.C = a.C AND sc.score >= a.score GROUP BY sc.C,sc.S,sc.score HAVING COUNT(sc.C)>3 ORDER BY a.C,a.score DESC
方法二：
SELECT a.* FROM
sc a
WHERE
(SELECT COUNT(*)
FROM sc
WHERE sc.C =a.C
AND a.score<sc.score) <3
ORDER BY a.C,a.score DESC
解析：
与方法一同理，复制一个表进行count计数