一个简单的分段统计的问题:student 表{id,name,score} 字段,统计各个分数段的人数。
规则:60以下不及格,60-70及格,70-80中等,80-90良好,90-100优秀。
二、代码:
解析
sum为求和函数,用于统计分数段人数
case做判断
select...from student 指定在student表中进行操作
score 指定在score字段中进行操作
as 用于定义列名
- SELECT
- sum(CASE when score<60 then 1 else 0 end) AS '不及格',
- sum(CASE when score>=60 and score<70 then 1 else 0 end) AS '及格',
- sum(CASE when score>=70 and score<80 then 1 else 0 end) AS '中等',
- sum(CASE when score>=80 and score<90 then 1 else 0 end) AS '良好',
- sum(CASE when score>=90 and score<=100 then 1 else 0 end) AS '优秀'
- from student;