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)',