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;