mysql 分数段_mysql 分段统计数据

一个简单的分段统计的问题：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;