【SQL实战】期末考试，如何统计学生成绩

 

 

 

年底临近，这两天各小学都进入期末考试阶段了。考试结束，就要对学生成绩进行统计。有趣的是，现在学校提供的成绩单上不直接写明分数了，而是一个等级，例如：优秀、良好、合格、不及格。至少北京是这样子的。
言归正传，我们怎么根据成绩表来统计优良差呢？

drop table test_score, test_subject;

-- 学生考试成绩表（学生、科目、成绩）。这里为了方便测试，直接使用临时表

create temporary table test_score
select '张小明' as name, 'Chinese' as 'subject', 89.5 as 'score' union all
SELECT '佩奇', 'Chinese', 100 UNION ALL
SELECT '小哪吒', 'Chinese', 38 UNION ALL
SELECT '乔治', 'Chinese', 95 UNION ALL
SELECT '乔治', 'English', 55 UNION ALL
SELECT '米小圈', 'English', 82 UNION ALL
select '佩奇', 'English', 98 ;

select * from test_score;

name subject score
--------- ------- --------
张小明 Chinese 89.5
佩奇 Chinese 100.0
小哪吒 Chinese 38.0
乔治 Chinese 95.0
乔治 English 55.0
米小圈 English 82.0
佩奇 English 98.0

-- §§§【学生成绩单】

-- 【假定分三档来统计：优-Excellent、良-Good、差-Lost ；其中， 80分以上为”优“，60~85为”良“，60分以下为”差“】
select name, subject, CASE WHEN score>85 THEN 'Excellent'
WHEN score>=60 AND score<85 THEN 'Good'
ELSE 'Lost' END as '成绩'
from test_score
order by 1,2;

name subject 成绩
--------- ------- -----------
乔治 Chinese Excellent
乔治 English Lost
佩奇 Chinese Excellent
佩奇 English Excellent
小哪吒 Chinese Lost
张小明 Chinese Excellent
米小圈 English Good

-- §§§【语文老师需要统计语文成绩优良差的学生人数】

select sum(case when score>85 then 1 else 0 end) as 'Excellent'
, SUM(CASE WHEN score>=60 and score <85 THEN 1 ELSE 0 end) AS 'Good'
, SUM(CASE WHEN score<60 THEN 1 ELSE 0 end) AS 'Lost'
from test_score
where subject = 'Chinese';

Excellent Good Lost
--------- ------ --------
3 0 1

-- §§§【每一科成绩的优良差的学生人数】

select subject, sum(case when score>85 then 1 else 0 end) as 'Excellent'
, SUM(CASE WHEN score>=60 and score <85 THEN 1 ELSE 0 end) AS 'Good'
, SUM(CASE WHEN score<60 THEN 1 ELSE 0 end) AS 'Lost'
from test_score
group by subject;

subject Excellent Good Lost
------- --------- ------ --------
Chinese 3 0 1
English 1 1 1

-- §§§【增加统计难度----->语文老师要统计语文成绩优良差的人数，并统计各档的总成绩 和 平均成绩】

-- ** 这时，我们再用上面的sql就显得吃力了。 办法总比困难多， 看下面的SQL
select CASE WHEN score>85 THEN 'Excellent'
WHEN score>=60 AND score<85 THEN 'Good'
ELSE 'Lost' END as 'Level'
, count(1) as '总人数'
, sum(score) AS '总成绩'
, avg(score) AS '平均成绩'
FROM test_score
WHERE SUBJECT = 'Chinese'
group by case when score>85 then 'Excellent'
when score>=60 and score<85 then 'Good'
else 'Lost' end;

Level 总人数 总成绩 平均成绩
--------- --------- --------- --------------
Excellent 3 284.5 94.83333
Lost 1 38.0 38.00000

-- §§§【学期结束，班主任老师需要给成绩优秀的学生颁发奖状】

-- ** 假定每科的考试分数在85分以上为优秀学生。

-- 学科表（语文、数学、英语，这些课程）。这里为了方便测试，直接使用临时表
CREATE TEMPORARY TABLE test_subject SELECT DISTINCT SUBJECT FROM test_score;

SELECT NAME, COUNT(1) as '参加考试科目数', SUM(score) as '总成绩'
FROM test_score
GROUP BY NAME
HAVING MIN(score)>=85
AND COUNT(1) = (SELECT COUNT(1) FROM test_subject);

NAME 参加考试科目数 总成绩
------ --------------------- -----------
佩奇 2 198.0