MySQL行列转换函数 -- group_concat

MySQL行列转换函数 – group_concat

1、环境搭建

CREATE TABLE temp_dep(
	id INT,
	depId INT,
	name varchar(20),
	age INT,
	address varchar(100),
	brithday date
);


INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (1, 102, '张三', 25, '云南昆明', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (2, 101, '李四', 22, '四川成都', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (3, 103, '王五', 25, '云南昆明', str_to_date('2000-05-19 17:15:33','%Y-%m-%d %T'));
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (4, 102, '赵六', 23, '湖北武汉', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (5, 101, '钱七', 27, '云南昆明', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (6, 103, '小六', 28, '四川成都', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (7, 102, '小七', 26, '湖北武汉', str_to_date('2011-05-21 17:15:33','%Y-%m-%d %T'));
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (8, 101, '小八', 22, '广东深圳', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (9, 103, '小九', 25, '广东深圳', null);

SQL>SELECT * FROM temp_dep;
ID	DEPID	NAME	AGE	 ADDRESS	BRITHDAY
1	102	    张三		25	云南昆明	[NULL]
2	101		李四		22	四川成都	[NULL]
3	103		王五		25	云南昆明	2000-05-19 17:15:33
4	102		赵六		23	湖北武汉	[NULL]
5	101		钱七		27	云南昆明	[NULL]
6	103		小六		28	四川成都	[NULL]
7	102		小七		26	湖北武汉	2011-05-21 17:15:33
8	101		小八		22	广东深圳	[NULL]
9	103		小九		25	广东深圳	[NULL]

2、GROUP_CONCAT语法规则

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
expr：表达式或列名
str_val：分隔符

3、案例

-- 默认以","为分隔符进行拼接
mysql> select group_concat(name) from temp_dep;
group_concat(name)
张三,李四,王五,赵六,钱七,小六,小七,小八,小九

-- 指定";"为分隔符，且按年龄排序后再拼接
mysql> select group_concat(name order by name separator ';') from temp_dep;
group_concat(name order by name separator ';')
李四;钱七;王五;小八;小九;小六;小七;张三;赵六

-- 分组拼接
mysql> select age,group_concat(name order by age separator ';') from temp_dep group by age;
age	group_concat(name order by age separator ';')
22	小八;李四
23	赵六
25	小九;王五;张三
26	小七
27	钱七
28	小六