mysql中好用的函数group_concat

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

完整的语法如下：

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

比如建立一个模拟的表；
CREATE TABLE `group_c` (
`parent_id` int(11) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO group_c(parent_id, child_id)
VALUES (1,1),(1,2),(1,3),(2,1),(2,4),(1,4),(2,6),(3,1),(3,2),(4,1),(4,1),(1,1),(5,0);

查询一下：
SELECT DISTINCT
    -> parent_id, child_id
    -> FROM group_c
    -> 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 group_c
    -> 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; 

又比如；
mysql> SELECT DISTINCT
    -> parent_id, GROUP_CONCAT(DISTINCT child_id ORDER BY child_id) AS child_id_list
    -> FROM group_c
    -> 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) – 每个工程师要回答的问题

-- 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)