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

最新推荐文章于 2025-04-29 12:49:18 发布

原创最新推荐文章于 2025-04-29 12:49:18 发布 · 449 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

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

28 篇文章

订阅专栏

本文介绍了一种使用SQL查询实现对学生各科成绩进行统计分析的方法，包括最高分、最低分、平均分及其分布比率，并展示了如何将这些数据与课程表关联以获取更详细的信息。

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;

在这里插入图片描述