年底臨近，這兩天各小學都進入期末考試階段了，考試結束，就要對學生成績進行統計，有趣的是，現在學校提供的成績單上不直接寫明分數了，而是一個等級，例如：優秀、良好、合格、不及格，至少北京是這樣子的，
言歸正傳，我們怎么根據成績表來統計優良差呢？
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/413181.html
標籤：其他