mysql 分段統計資料

標籤：mysq   student   sum   欄位   bsp   subject   問題   and   sel   

一個簡單的分段統計的問題：student 表{id,name,score} 欄位，統計各個分數段的人數。規則：60以下不及格，60-80良，80-100優。

SELECT
sum(CASE when score<60 then 1 else 0 end) AS ‘不及格‘,
sum(CASE when score>=60 and score<=80 then 1 else 0 end) AS ‘良‘,
sum(CASE when score>80 then 1 else 0 end) AS ‘優‘
FROM student;

 

SELECT 
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score<60) 不及格,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score BETWEEN 60 AND 80) 良,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score>80) 優
FROM stuscore a GROUP BY a.subject;

 

mysql 分段統計資料