SQL笔试经典50题及答案解析（21-50题）

21、查询不同老师所教不同课程平均分从高到低显示

SELECT t2.tid, 
	   t1.cid, 
	   t2.cname, 
	   AVG(t1.score) as avg_score
FROM sc t1 
	 LEFT JOIN course t2 
	 ON t1.cid = t2.cid
GROUP BY t1.cid
ORDER BY avg_score DESC;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

# mysql 8.0以上版本：窗口函数
SELECT sid, cid, score, rank_num
FROM
	(SELECT sid, cid, score,
			rank() over(PARTITION by cid ORDER BY score DESC) as rank_num
	FROM sc) t1
WHERE rank_num >= 2 and rank_num <= 3;
# 使用rank() over()跳跃排序，可能存在几个并列第一而没有第2-3序号的情况

# 关联子查询-实现跳跃排序的功能
SELECT *
FROM sc t1
WHERE (SELECT COUNT(*) 
	   FROM sc t2 
	   WHERE t1.cid = t2.cid AND t1.score < t2.score) in (1,2)
ORDER BY t1.cid, t1.score DESC;

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

# 分布特征用case when函数
 SELECT t1.cid, t2.cname,
		SUM(CASE WHEN t1.score >= 85 THEN 1 ELSE 0 END) AS 'num_[85,100]',
		SUM(CASE WHEN t1.score >= 70 AND t1.score < 85 THEN 1 ELSE 0 END) AS 'num_[70,85)',
		SUM(CASE WHEN t1.score >= 60 AND t1.score < 70 THEN 1 ELSE 0 END) AS 'num_[60,70)',