sql统计各分数段人数示例

2024-07-06 17:55| 来源: 网络整理| 查看: 265

经常需要进行数据统计分析，可以结合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

【本文地址】

公司简介

联系我们