年关将至，这两天所有小学都进入了期末考试阶段。在考试结束时，有必要清点学生的分数。有趣的是，学校提供的成绩单现在并不直接标明分数，而是一个等级，如优秀、良好、合格、不及格。至少北京是这样的。
[En]
The end of the year is approaching, these two days all primary schools have entered the final examination stage. At the end of the exam, it is necessary to count the students’ scores. Interestingly, the transcript provided by the school now does not directly indicate the score, but a grade, such as excellent, good, qualified and failing. At least Beijing is like this.
回到正题，我们如何根据计分表来计算好与坏？
[En]
Back to the point, how can we count the good and bad according to the score sheet?
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 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
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
Level 总人数 总成绩 平均成绩
佩奇 2 198.0
Original: https://www.cnblogs.com/buguge/p/15788331.html
Author: buguge
Title: 【SQL实战】期末考试，如何统计学生成绩
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507884/
转载文章受原作者版权保护。转载请注明原作者出处！