mysql 分组统计 及 统计结果横向展示

本文主要介绍mysql常用的两种分组情况：

• 按数值范围分组，并横向展示
• 按类别值分组，并横向展示

PS：为直观展示，最后横向展示的字段名包含中文（真实场景中不推荐使用！！！）

范围分组

需求说明

1. 对总分范围进行统计，示例如下

总分范围 range	总数
< 60	1
(60, 70]	10
(70, 80]	20
(80, 90]	30
(90, 100]	5

2. 希望将上述分组后的结果，变更为横向展示，如未及格、D、C、B、A级别各多少人

A	B	C	D	未及格
5	30	20	10	1

示例

1. 创建表及数据

CREATE TABLE `t_student` (
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
	`score` INT(11) NOT NULL DEFAULT '0',
	`name` VARCHAR(64) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE
) COMMENT='学生表' COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (1, 58, '王一');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (2, 63, '赵一');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (3, 68, '李一');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (4, 71, '德康');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (5, 77, '古月');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (6, 75, '刘一');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (7, 89, '李二');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (8, 89, '李三');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (9, 88, '李四');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (10, 86, '李武');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (11, 100, '张三');
INSERT INTO `t_student` (`id`, `score`, `name`) VALUES (12, 95, '王五');

2. 分组及横向展示

SELECT 
	sum(case when `score` < 60 then 1 ELSE 0 END) AS '不及格',
	sum(case when `score` < 70 AND `score` >= 60 then 1 ELSE 0 END) AS D,
	sum(case when `score` < 80 AND `score` >= 70 then 1 ELSE 0 END) AS C,
	sum(case when `score` < 90 AND `score` >= 80 then 1 ELSE 0 END) AS B,
	sum(case when `score` <= 100 AND `score` >= 90 then 1 ELSE 0 END) AS A
FROM t_student 

sum 与 case when组合

一般使用case when进行范围区分（简要分组），再利用count进行统计

类别值分组

需求说明

1. 对类别进行统计，示例如下

类别(0：语文；1：数学；2：英语；3：体育)	报名人数
0	4
1	3
2	2
3	4

2. 希望将上述分组后的结果，变更为横向展示，如选择语文、数学、英语及体育各多少人

语文	数学	英语	体育
4	3	2	4

示例

1. 创建表及数据

CREATE TABLE `t_course` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`type` INT(11) NOT NULL COMMENT '类型（0：语文；1：数学；2：英语；3：体育）',
	`student_id` INT(11) NOT NULL COMMENT '学生id',
	PRIMARY KEY (`id`) USING BTREE
) COMMENT='课程表' COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;

INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (1, 0, 1);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (2, 0, 2);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (3, 0, 3);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (4, 0, 4);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (5, 1, 1);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (6, 1, 3);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (7, 2, 1);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (8, 2, 2);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (9, 2, 4);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (10, 3, 1);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (11, 3, 2);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (12, 3, 3);
INSERT INTO `t_course` (`id`, `type`, `student_id`) VALUES (13, 3, 4);

2. 用group by 对类别进行分组

SELECT `type`, COUNT(*) AS counts FROM t_course GROUP BY `type` 

3. 将分组后的纵向结果，换为横向展示

SELECT 
	SUM(case when t.`type` = 0 then t.counts ELSE 0 END) AS '语文',
	SUM(case when t.`type` = 1 then t.counts ELSE 0 END) AS '数学',
	SUM(case when t.`type` = 2 then t.counts ELSE 0 END) AS '英语',
	SUM(case when t.`type` = 3 then t.counts ELSE 0 END) AS '体育'
FROM 
(
	SELECT 
		`type`, COUNT(*) AS counts 
	FROM t_course 
	GROUP BY `type` 
)t

group by 、 sum 与 case when组合

分组统计：

• group by 分组 count 个数
• case when 按条件区分，sum求和