SQL进行条件统计：GROUP_CONCAT 行转列

 

 （编写不易，转载请注明： https://shihlei.iteye.com/blog/2442053）

一 概述

    继续支持运营出统计数据，需要行转列，看来下 GROUP_CONCAT 函数，做个记录。

 

二 场景

1) 表结构

员工表employee 

id, name , type , age, leader

 

CREATE TABLE `employee`(
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(100) NOT NULL COMMENT '姓名',
`type` VARCHAR(2) NOT NULL COMMENT '类别 A，B，C',
`age` INT(2) NOT NULL COMMENT '年龄',
`leader` VARCHAR(100) NOT NULL COMMENT '组长',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

insert into `employee`(`name`,`type`,`age`,`leader`) values 
('name_10','A',10,'tom'), 
('name_45','A',45,'tom'), 
('name_47','A',47,'mike'), 
('name_60','A',60,'tom'), 
('name_12','A',12,'tom'), 
('name_70','A',70,'tom'), 
('name_10','B',10,'mike'), 
('name_50','B',50,'tom'), 
('name_51','B',51,'tom');

 

 

2）统计需求

查询各个leader 下 年龄大于40岁的各类员工，按如下表头输出，按年龄从小到大

 

表头：leader，A类员工，B类员工

 

3）脚本

SELECT leader, 
GROUP_CONCAT(IF(`type`='A' AND `age` > 40,`name`,NULL) ORDER BY `age`) as 'A类员工',
GROUP_CONCAT(IF(`type`='B' AND `age` > 40,`name`,NULL) ORDER BY `age`) as 'B类员工'
FROM employee 
GROUP BY leader;

 

4) 结果

 

 

 

三 函数说明

1) 定义：

GROUP_CONCAT ( [DISTINCT] col_name1 [ORDER BY clause]  [SEPARATOR str_val] ) 

 

2）说明：

用于行转列，依赖group by分组，可以配合IF函数进行条件拼接

• Dinstinct: 指定是否排重
• Order By: 指定排序字段
• Separator: 指定分隔符，默认逗号

3）关于拼接长度限制： 

GROUP_CONCAT 通过 group_concat_max_len 变量 设置了 拼接的长度限制，超过最大显示会被截断，可以通过修改该变量保证不被截断

 

查看长度限制 ：show variables like 'group_concat_max_len';

 

修改：

SET GLOBAL group_concat_max_len = 102400;

SET SESSION group_concat_max_len = 102400; 

  

或者 设置 group_concat_max_len = -1 指定最大