练习17.聚合函数、case when、concat函数

最新推荐文章于 2023-06-16 17:08:53 发布

百里守约学编程

最新推荐文章于 2023-06-16 17:08:53 发布

阅读量327

点赞数 1

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

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

版权

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

28 篇文章 11 订阅

订阅专栏

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

第一步：按课程分组，统计各课程分数在每个区间段的人数

SELECT 
cid,
SUM(CASE WHEN score <= 100 AND score >= 85 THEN 1 ELSE 0 END) AS '[100-85]',
SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END) AS '(85-70]',
SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END) AS '(70-60]',
SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END) AS '(60-0]'
FROM sc
GROUP BY cid;

在这里插入图片描述

第二步：各区间人数除以该课程总人数即为百分比

SELECT 
cid,
SUM(CASE WHEN score <= 100 AND score >= 85 THEN 1 ELSE 0 END)/COUNT(*) AS '[100-85]',
SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END)/COUNT(*) AS '(85-70]',
SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS '(70-60]',
SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END)/COUNT(*) AS '(60-0]'
FROM sc
GROUP BY cid;

在这里插入图片描述
第三步：期望是带%的百分比，所以可以乘以100后，使用concat函数连接%

![SELECT 
cid,
CONCAT(SUM(CASE WHEN score <= 100 AND score >= 85 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '\[100-85\]',
CONCAT(SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '(85-70\]',
CONCAT(SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '(70-60\]',
CONCAT(SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '(60-0\]'
FROM sc
GROUP BY cid;](https://img-blog.csdnimg.cn/885b0b9b376b4530b139f1caccdfe96b.png)

在这里插入图片描述
第四步：关联课程表，获取课程名称

SELECT
a.*,b.Cname
FROM (
SELECT 
cid,
CONCAT(SUM(CASE WHEN score <= 100 AND score > 85 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '[100-85]',
CONCAT(SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '(85-70]',
CONCAT(SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '(70-60]',
CONCAT(SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '(60-0]'
FROM sc
GROUP BY cid) a
LEFT JOIN course b
ON a.cid = b.cid;