sql统计各分数段人数示例

经常需要进行数据统计分析，可以结合sql语句中的sum记和、case分类来实现分类统计。

例如统计各分数段人数。90-100分数段内的标记为A，80-89分数段内的标记为B，70-79分数段内的标记为C，60-69分数段内的标记为D，小于60的标记为E，并统计各分数段的人数。

– 建表

CREATE TABLE `student` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `score` double(11,8) NOT NULL DEFAULT '0.00000000' COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=126424 DEFAULT CHARSET=utf8 COMMENT='学生表';

– 插入数据

INSERT INTO  student VALUES('1','wanwei',95);
INSERT INTO  student VALUES('2','shixiaoyan',90);
INSERT INTO  student VALUES('3','wangwu',70);
INSERT INTO  student VALUES('4','wsdfangwu',60);
INSERT INTO  student VALUES('5','sadf',50);
INSERT INTO  student VALUES('6','frgd',65);

– 统计各分数段人数

select sum(case when score between 90 and 100 then 1 else 0 end) as A,
sum(case when score between 80 and 89 then 1 else 0 end) as B,
sum(case when score between 70 and 79 then 1 else 0 end) as C,
sum(case when score between 60 and 69 then 1 else 0 end) as D,
sum(case when score<60 then 1 else 0 end) as E
from student

结果：