记录一次mysql统计数据将列查出的值转换为行名使用

 

统计表的结果是以党员为分组,将所有的活动前部已列的形式展示,统计每一个党员参与的各个活动的次数,没有参与为0次 

需要的最终统计结果预览如下

姓名	支部名称	活动1	活动2	活动3	活动4
党员1	党支部名称	1	1	1	1
党员2	党支部名称	0	0	0	1
党员3	党支部名称	0	0	0	2

表结构和数据

CREATE TABLE `t_party_event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '党员活动名称',
  `event_time` datetime DEFAULT NULL COMMENT '活动时间',
  `user_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '党员名称',
  `branch_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支部id',
  `branch_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支部名称',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='党员活动表';

INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('1', '活动1', '2020-12-05 14:34:02', '党员1', '党支部id', '党支部名称', '2020-12-05 14:34:40');
INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('2', '活动2', '2020-12-05 14:34:02', '党员1', '党支部id', '党支部名称', '2020-12-05 14:34:40');
INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('3', '活动3', '2020-12-05 14:34:02', '党员1', '党支部id', '党支部名称', '2020-12-05 14:34:40');
INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('4', '活动4', '2020-12-05 14:34:02', '党员2', '党支部id', '党支部名称', '2020-12-05 14:34:40');
INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('5', '活动4', '2020-12-05 14:34:02', '党员1', '党支部id', '党支部名称', '2020-12-05 14:34:40');
INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('6', '活动4', '2020-12-05 14:34:02', '党员3', '党支部id', '党支部名称', '2020-12-05 14:34:40');
INSERT INTO `oa-server`.`t_party_event` (`id`, `name`, `event_time`, `user_name`, `branch_id`, `branch_name`, `create_time`) VALUES ('7', '活动4', '2020-12-06 14:34:02', '党员3', '党支部id', '党支部名称', '2020-12-05 14:34:40');
 

sql编写过程

将党员的每次活动数量都统计出来

(党员的党支部名称即便重复也是正确的(因为即使党员名称不同,但有可能是一个支部),因为group by是将 user_name,branch_name,name三个字段为一个组来进行分组)

SELECT
    a.user_name,a.branch_name,a.`name`,count(a.id) as number
FROM
 t_party_event a 
GROUP BY
    a.user_name,a.branch_name,a.`name`
ORDER BY user_name

统计出来后,但是活动的名称是在一列的,想办法放为多个列,此处有一个问题是活动的名称是名称都已知的情况,如果名称为动态可变,则需要动态拼写sql

通过CASE WHEN 加MAX将所有的数量统计起来,并展示为多列

SELECT a.user_name '姓名',a.branch_name '支部名称',
max(CASE WHEN(a.name = '活动1') THEN number ELSE 0 END) '活动1',
max(CASE WHEN(a.name = '活动2') THEN number ELSE 0 END) '活动2',
max(CASE WHEN(a.name = '活动3') THEN number ELSE 0 END) '活动3',
max(CASE WHEN(a.name = '活动4') THEN number ELSE 0 END) '活动4'
FROM (
SELECT
	a.user_name,a.branch_name,a.`name`,count(a.id) as number
FROM
 t_party_event a 
GROUP BY
	a.user_name,a.branch_name,a.`name`
ORDER BY user_name
) a
GROUP BY a.user_name,a.branch_name