Day9 每日打卡 -简单可预期

求前几名

SELECT *
FROM s_course  sc1
WHERE (
SELECT COUNT(*)
FROM s_course  sc2
WHERE  sc1.c_id = sc2.c_id   # sc1.grade <sc2.grade 前二
AND sc1.grade > sc2.grade    #sc1.grade > sc2.grade 倒数前二
) <2  # 也就是比当前大的数只有一个 或者没有 , 对应学生的第二名和第一名
ORDER BY sc1.c_id,sc1.s_id,sc1.grade

参考：https://blog.csdn.net/weixin_44497013/article/details/107317719

使用自连接

不能求前1名

SELECT sc1.s_id,sc1.c_id,sc1.grade
FROM s_course sc1
LEFT JOIN (SELECT DISTINCT c_id, grade FROM s_course) sc2
ON sc1.c_id = sc2.c_id
AND sc1.grade < sc2.grade
GROUP BY s_id,c_id,grade
HAVING COUNT(1) <3
ORDER BY c_id,grade DESC

改进：

可以求出前1名

SELECT sc1.s_id,sc1.c_id,sc1.grade 
FROM s_course sc1   		# 将具有相同(c_id,grade)的元组去重
				            # 去重以后sc2 可能 比 sc1 少一些c_id ，所以需要左连接
JOIN (SELECT DISTINCT c_id, grade FROM s_course) sc2  
ON sc1.c_id = sc2.c_id    
AND sc1.grade <= sc2.grade     # 这里是关键
GROUP BY sc1.s_id,sc1.c_id,sc1.grade
HAVING COUNT(1) <=2   # 当sc1.grade <= sc2.grade,    COUNT(1) <=2 表示 求前两名
					  # 当sc1.grade >= sc2.grade,    COUNT(1) <=2 表示 求倒数的两名
ORDER BY sc1.c_id,sc1.grade DESC