练习14. 聚合函数、case when...then...else...end

百里守约学编程

于 2022-08-29 23:18:59 发布

阅读量314

点赞数 1

分类专栏： # MySQL练习经典45题文章标签：数据库 sql

本文链接：https://blog.csdn.net/YouMing_Li/article/details/126594428

版权

MySQL练习经典45题专栏收录该内容

28 篇文章 11 订阅

订阅专栏

14.查询各科成绩最高分、最低分和平均分

1. 以如下形式显示：课程ID，选修人数，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，课程name
2. 及格为：>=60 中等为：[70,80),优良为：[80,90),优秀为：>=90
3. 查询结果按人数降序排列，若人数相同，按课程号升序排列

第一步：锁定表，成绩表，课程表
在这里插入图片描述

第二步：聚合计算查出最高分，最低分等，使用case when区分分数阶段，这里可以用1和0来巧妙的求和符合某个条件的总人数，如下

SELECT 
cid,
COUNT(*) AS '选修人数',
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS avg_score,
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END), 
SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END), 
SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END), 
SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)
FROM sc
GROUP BY cid;

在这里插入图片描述

第三步：各种率计算，除以分组下的总人数即可

SELECT 
cid,
COUNT(*) AS '选修人数',
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS avg_score,
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS '及格率', 
SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)/COUNT(*) AS '中等率', 
SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优良率', 
SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优秀率'
FROM sc
GROUP BY cid;

在这里插入图片描述

第四步：关联课程表，获取课程名称，并按题目要求排序输出

SELECT 
a.*,b.Cname
FROM (
SELECT 
cid,
COUNT(*) AS ct,
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS avg_score,
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS '及格率', 
SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)/COUNT(*) AS '中等率', 
SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优良率', 
SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优秀率'
FROM sc
GROUP BY cid ) AS a 
LEFT JOIN course AS b
ON a.cid = b.cid
ORDER BY ct DESC,cid ASC;