MySQL函数group_concat

MySQL中，有个不错的函数group_concat，主要作用是用来应付如一对多情况的变体的

完整语法

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

比如建立一个模拟的表

CREATE TABLE `mimvp_group_concat` ( 
	`parent_id` int(11) DEFAULT NULL, 
	`child_id` int(11) DEFAULT NULL 
) ENGINE=InnoDB; 

INSERT INTO mimvp_group_concat(parent_id, child_id) 
VALUES (1,1),(1,2),(1,3),(2,1),(2,4),(1,4),(2,3),(2,6),(3,1),(3,2),(4,1),(4,1),(1,1),(5,0);

查询一下

select parent_id, child_id from mimvp_group_concat order by parent_id, child_id;
+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 |        1 |
|         1 |        1 |
|         1 |        2 |
|         1 |        3 |
|         1 |        4 |
|         2 |        1 |
|         2 |        3 |
|         2 |        4 |
|         2 |        6 |
|         3 |        1 |
|         3 |        2 |
|         4 |        1 |
|         4 |        1 |
|         5 |        0 |
+-----------+----------+
14 rows in set (0.00 sec)

select distinct parent_id, child_id from mimvp_group_concat order by parent_id;
+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 |        1 |
|         1 |        2 |
|         1 |        3 |
|         1 |        4 |
|         2 |        1 |
|         2 |        3 |
|         2 |        4 |
|         2 |        6 |
|         3 |        1 |
|         3 |        2 |
|         4 |        1 |
|         5 |        0 |
+-----------+----------+
12 rows in set (0.00 sec)

可以看到,parent_id和child_id是一对多的情况，而如果使用group_concat函数，则

SELECT DISTINCT parent_id, group_concat(DISTINCT child_id ORDER BY child_id) AS child_id_list 
FROM mimvp_group_concat 
group by parent_id 
order by parent_id;

+-----------+---------------+
| parent_id | child_id_list |
+-----------+---------------+
|         1 | 1,2,3,4       |
|         2 | 1,3,4,6       |
|         3 | 1,2           |
|         4 | 1             |
|         5 | 0             |
+-----------+---------------+
5 rows in set (0.00 sec)

可以看到，其具体区别了，默认是用逗号分隔的；

还可以在group_concat中做文章，比如：

select id, group_concat(distinct name) from aa group by id;

这里是在group_concat(distinct name)列中，去掉重复的name;

select id, group_concat(name order by name desc) from aa group by id;

又比如；

SELECT DISTINCT parent_id, group_concat(DISTINCT child_id ORDER BY child_id) AS child_id_list 
FROM mimvp_group_concat 
group by parent_id 
ORDER BY parent_id; 

+-----------+---------------+
| parent_id | child_id_list |
+-----------+---------------+
|         1 | 1,2,3,4       |
|         2 | 1,3,4,6       |
|         3 | 1,2           |
|         4 | 1             |
|         5 | 0             |
+-----------+---------------+
5 rows in set (0.00 sec)

来点复杂的例子，比如
engineers (id, name, surname, URL) – 工程师
customers (id, company name, URL) –客户
issues (id, customer_id, description) – 每个客户咨询的问题
workflow (id, engineer_id, issue_id) – 每个工程师要回答的问题

Java代码

-- Engineers  
CREATE TABLE engineers (  
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  
e_name VARCHAR(30) NOT NULL,  
e_surname VARCHAR(30) NOT NULL,  
url VARCHAR(255) NOT NULL,  
PRIMARY KEY (id)  
) ENGINE=InnoDB;  
-- Customers  
CREATE TABLE customers (  
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  
company_name VARCHAR(30) NOT NULL,  
url VARCHAR(255) NOT NULL,  
PRIMARY KEY (id)  
) ENGINE=InnoDB;  
-- Issues (Issue-Customer)  
CREATE TABLE issues (  
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,  
customer_id VARCHAR(30) NOT NULL,  
description TEXT,  
PRIMARY KEY (id)  
) ENGINE=InnoDB;  
-- Workflow (Action: Engineer-Issue(Customer))  
CREATE TABLE workflow (  
action_id INT UNSIGNED NOT NULL AUTO_INCREMENT,  
engineer_id SMALLINT UNSIGNED NOT NULL,  
issue_id SMALLINT UNSIGNED NOT NULL,  
PRIMARY KEY (action_id)  
) ENGINE=InnoDB;  
INSERT INTO engineers (e_name, e_surname, url)  
VALUES  
('Miguel', 'Nieto', 'http://www.percona.com/about-us/our-team/miguel-angel-nieto'),  
('Marcos', 'Albe', 'http://www.percona.com/about-us/our-team/marcos-albe'),  
('Valerii', 'Kravchuk', 'http://www.percona.com/about-us/our-team/valerii-kravchuk'),  
('Michael', 'Rikmas', 'http://www.percona.com/about-us/our-team/michael-rikmas');  
INSERT INTO customers (company_name, url)  
VALUES  
('OT','http://www.ovaistariq.net/'),  
('PZ','http://www.peterzaitsev.com/'),  
('VK','http://MySQLentomologist.blogspot.com/'),  
('FD','http://www.lefred.be/'),  
('AS','http://MySQLunlimited.blogspot.com/'),  
('SS','https://www.flamingspork.com/blog/');  
INSERT INTO issues(customer_id, description)  
VALUES  
(1,'Fix replication'),  
(2,'Help with installation of Percona Cluster'),  
(3,'Hardware suggestions'),  
(4,'Error: no space left'),  
(5,'Help with setup daily backup by Xtrabackup'),  
(6,'Poke sales about Support agreement renewal'),  
(4,'Add more accounts for customer'),  
(2,'Create Hot Fix of Bug 1040735'),  
(1,'Query optimisation'),  
(1,'Prepare custom build for Solaris'),  
(2,'explain about Percona Monitoring plugins'),  
(6,'Prepare access for customer servers for future work'),  
(5,'Decribe load balancing for pt-online-schema-change'),  
(4,'Managing deadlocks'),  
(1,'Suggestions about buffer pool size');  
INSERT INTO workflow (engineer_id, issue_id)  
VALUES (1,1),(4,2),(2,3),(1,4),(3,5),(2,6),(3,7),(2,8),(2,9),(1,10),(3,11),(2,12),(2,13),(3,14),(1,15),(1,9),(4,14),(2,9),(1,15),(3,10),(4,2),(2,15),(4,8),(4,4),(3,11),(1,7),(3,7),(1,1),(1,9),(3,4),(4,3),(1,5),(1,7),(1,4),(2,4),(2,5);

则：列出每个工程师要回答的问题：

SELECT CONCAT (e_name, ' ', e_surname) AS engineer, group_concat(DISTINCT issue_id, ' (', c.company_name,')' ORDER BY issue_id SEPARATOR ', ' ) AS 'issue (customer)' 
FROM workflow w, engineers e, customers c, issues i 
WHERE w.engineer_id = e.id AND w.issue_id = i.id AND i.customer_id = c.id 
GROUP BY e.id 
ORDER BY e_name, e_surname;

+------------------+---------------------------------------------------------------------------+
| engineer         | issue (customer)                                                          |
+------------------+---------------------------------------------------------------------------+
| Marcos Albe      | 3 (VK), 4 (FD), 5 (AS), 6 (SS), 8 (PZ), 9 (OT), 12 (SS), 13 (AS), 15 (OT) |
| Michael Rikmas   | 2 (PZ), 3 (VK), 4 (FD), 8 (PZ), 14 (FD)                                   |
| Miguel Nieto     | 1 (OT), 4 (FD), 5 (AS), 7 (FD), 9 (OT), 10 (OT), 15 (OT)                  |
| Valerii Kravchuk | 4 (FD), 5 (AS), 7 (FD), 10 (OT), 11 (PZ), 14 (FD)                         |
+------------------+---------------------------------------------------------------------------+
4 rows in set (0.00 sec)

列出每个客户对应的问题，以及这些问题由什么工程师面对：

SELECT c.company_name AS company, group_concat(DISTINCT issue_id, ' (', engineer_list, ')' ORDER BY issue_id SEPARATOR ', ' ) AS issue 
FROM workflow w, engineers e, customers c, issues i, 
  (SELECT i.id AS i_id, group_concat(DISTINCT CONCAT(e_name, ' ', e_surname) ORDER BY e_name SEPARATOR ', ') AS engineer_list 
   FROM workflow w, engineers e, issues i 
   WHERE w.engineer_id = e.id AND w.issue_id = i.id 
   GROUP BY i.id) AS e_list 
WHERE w.engineer_id = e.id AND w.issue_id = i.id AND i.customer_id = c.id AND w.issue_id = e_list.i_id 
GROUP BY c.id 
ORDER BY c.company_name;

+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| company | issue (engineer)                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| AS      | 5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe)                                                                          |
| FD      | 4 (Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk), 7 (Miguel Nieto, Valerii Kravchuk), 14 (Michael Rikmas, Valerii Kravchuk) |
| OT      | 1 (Miguel Nieto), 9 (Marcos Albe, Miguel Nieto), 10 (Miguel Nieto, Valerii Kravchuk), 15 (Marcos Albe, Miguel Nieto)                       |
| PZ      | 2 (Michael Rikmas), 8 (Marcos Albe, Michael Rikmas), 11 (Valerii Kravchuk)                                                                 |
| SS      | 6 (Marcos Albe), 12 (Marcos Albe)                                                                                                          |
| VK      | 3 (Marcos Albe, Michael Rikmas)                                                                                                            |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

group_concat 使用实例

group_concat()是MySQL数据库提供的一个函数，通常跟GROUP BY一起用,具体可参考MySQL官方文挡:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat。

语法:

group_concat([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC][,col_name ...]] [SEPARATOR str_val])

1. 例如
SELECT student_id, group_concat(courses_id) AS courses FROM student_courses WHERE student_id=2GROUP BY student_id; 
+------------+---------+ 
| student_id | courses | 
+------------+---------+ 
| 2 | 3,4,5 |
+------------+---------+ 
这 就不需要用php循环了
$row = $pdo->query("SELECT student_id, group_concat(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id");
$result = explode(',', $row['courses']);

2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：

SELECT student_id, group_concat(courses_id SEPARATOR '|||') AS courses FROM student_courses WHEREstudent_id=2 GROUP BY student_id;
+------------+---------+ 
| student_id | courses | 
+------------+---------+ 
| 2 | 3|||4|||5 |
+------------+---------+ 

3.除此之外，还可以对这个组的值来进行排序再连接成字符串，例如按courses_id降序来排：
SELECT student_id, group_concat(courses_id ORDER BY courses_id DESC) AS courses FROMstudent_courses WHERE student_id=2 GROUP BY student_id;
+------------+---------+ 
| student_id | courses | 
+------------+---------+ 
| 2 | 5,4,3 |
+------------+---------+

4. 需要注意的

a. int字段的连接陷阱

当你用group_concat的时候请注意，连接起来的字段如果是int型，一定要转换成char再拼起来，
否则在你执行后(ExecuteScalar或者其它任何执行SQL返回结果的方法)返回的将不是一个逗号隔开的串，
而是byte[]。

该问题当你在SQLyog等一些工具中是体现不出来的，所以很难发现。

select group_concat(ipaddress) from t_ip 返回逗号隔开的串
select group_concat(id) from t_ip 返回byte[]
select group_concat(CAST(id as char)) from t_dep 返回逗号隔开的串
select group_concat(Convert(id , char)) from t_dep 返回逗号隔开的串

附Cast,convert的用法：
CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

这个类型可以是以下值其中的一个：

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

b. 长度陷阱

用了group_concat后，select里如果使用了limit是不起作用的.
用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。

使用group_concat_max_len系统变量，你可以设置允许的最大长度。
程序中进行这项操作的语法如下，其中 val 是一个无符号整数：
SET [SESSION | GLOBAL] group_concat_max_len = val;
若已经设置了最大长度，则结果被截至这个最大长度。

在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后，重新打开SQLyog，设置就会生效。

参考推荐：

MySQL中distinct和group by性能比较

MySQL中case when语句用法

MySQL 查询语句取整数或小数

MySQL 字段的添加前缀、去掉前缀等字符串操作