MySQL group_concat()函式
在本教學中，您將學習如何使用MySQL GROUP_CONCAT函式將分組中的字串與各種選項進行連線。
MySQL GROUP_CONCAT函式介紹
MySQL GROUP_CONCAT()函式將組中的字串連線成為具有各種選項的單個字串。
下面說明了GROUP_CONCAT()函式的語法：
GROUP_CONCAT(DISTINCT expression
ORDER BY expression
SEPARATOR sep);
以下是演示GROUP_CONCAT()函式如何工作的一個範例。
USE testdb;
CREATE TABLE t (
v CHAR
);
INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');
SELECT
GROUP_CONCAT(DISTINCT v
ORDER BY v ASC
SEPARATOR ';')
FROM
t;
-- SELECT v FROM t GROUP BY v;
執行上面查詢語句，得到以下結果 -
+---------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT v
ORDER BY v ASC
SEPARATOR ';') |
+---------------------------------------------------------------------+
| A;B;C |
+---------------------------------------------------------------------+
1 row in set
註：上面語句類似於把
SELECT v FROM t GROUP BY v;語句的結果串接起來。
參考以下圖解 -
DISTINCT子句用於在連線分組之前消除組中的重複值。
ORDER BY子句允許您在連線之前按升序或降序排序值。 預設情況下，它按升序排序值。 如果要按降序對值進行排序，則需要明確指定DESC選項。
SEPARATOR指定在組中的值之間插入的文字值。如果不指定分隔符，則GROUP_CONCAT函式使用逗號(，)作為預設分隔符。
GROUP_CONCAT函式忽略NULL值，如果找不到匹配的行，或者所有引數都為NULL值，則返回NULL。
GROUP_CONCAT函式返回二進位制或非二進位制字串，這取決於引數。 預設情況下，返回字串的最大長度為1024。如果您需要更多的長度，可以通過在SESSION或GLOBAL級別設定group_concat_max_len系統變數來擴充套件最大長度。
MySQL GROUP_CONCAT範例
讓我們來看看範例資料庫(yiibaidb)中的customers表，其表結構如下所示 -
mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int(11) | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set
要獲取客戶所在的國家/地區，以逗號分隔的字串，您可以使用GROUP_CONCAT函式，如下所示：
SELECT
GROUP_CONCAT(country)
FROM
customers;
執行上面查詢語句，得到以下結果 -
然而，一些客戶位於同一個國家。要刪除重複的國家/地區名稱，請將DISTINCT子句新增到函式，如下查詢：
mysql> SELECT
GROUP_CONCAT(DISTINCT country)
FROM
customers;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT country) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| France,USA,Australia,Norway,Poland,Germany,Spain,Sweden,Denmark,Singapore,Portugal,Japan,Finland,UK,Ireland,Canada,Hong Kong,Italy,Switzerland,Netherlands,Belgium,New Zealand,South Africa,Austria,Philippines,Russia,Israel |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
如果國家的名稱按升序排列，則可讀性更高。要在連線之前排序國家的名稱，請使用ORDER BY子句如下：
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country)
FROM
customers;
執行上面查詢語句，得到以下結果 -
mysql> SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country)
FROM
customers;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT country
ORDER BY country) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
要將返回的字串的預設分隔符從逗號(，)更改為分號(;)，請使用SEPARATOR子句作為以下查詢：
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country
SEPARATOR ';')
FROM
customers;
執行上面查詢語句，得到以下結果 -
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT country
ORDER BY country
SEPARATOR ';') |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Australia;Austria;Belgium;Canada;Denmark;Finland;France;Germany;Hong Kong;Ireland;Israel;Italy;Japan;Netherlands;New Zealand;Norway;Philippines;Poland;Portugal;Russia;Singapore;South Africa;Spain;Sweden;Switzerland;UK;USA |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
經過上面幾個簡單範例，現在您應該知道GROUP_CONCAT函式是如何工作，現在把它應用在一個實際的例子中。
每個客戶都有一個或多個銷售代表。 換句話說，每個銷售人員都負責一個或多個客戶。 要了解誰負責哪些客戶，使用inner join子句如下：
SELECT
employeeNumber, firstname, lastname, customername
FROM
employees
INNER JOIN
customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY firstname , lastname;
執行上面查詢語句，得到以下結果 -
+----------------+-----------+-----------+------------------------------------+
| employeeNumber | firstname | lastname | customername |
+----------------+-----------+-----------+------------------------------------+
| 1611 | Andy | Fixter | Souveniers And Things Co. |
| 1611 | Andy | Fixter | Australian Collectables, Ltd |
| 1611 | Andy | Fixter | Australian Gift Network, Co |
| 1611 | Andy | Fixter | Australian Collectors, Co. |
| 1611 | Andy | Fixter | Anna's Decorations, Ltd |
| 1504 | Barry | Jones | Baane Mini Imports |
| 1504 | Barry | Jones | Toms Spezialitten, Ltd |
************* 此處省略了一大波資料 *********************************************
| 1216 | Steve | Patterson | Auto-Moto Classics Inc. |
| 1216 | Steve | Patterson | Gifts4AllAges.com |
| 1216 | Steve | Patterson | FunGiftIdeas.com |
| 1216 | Steve | Patterson | Diecast Classics Inc. |
| 1216 | Steve | Patterson | Online Diecast Creations Co. |
+----------------+-----------+-----------+------------------------------------+
122 rows in set
現在，我們可以按員工編號對結果集進行分組，並使用GROUP_CONCAT函式連線正在負責員工的所有員工，如下所示：
SELECT
employeeNumber,
firstName,
lastName,
GROUP_CONCAT(DISTINCT customername
ORDER BY customerName)
FROM
employees
INNER JOIN
customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;
上面查詢語句，執行結果如下 -
如下所示的結果更容易閱讀。
具有CONCAT_WS函式的MySQL GROUP_CONCAT函式的範例
有時，GROUP_CONCAT函式可以與CONCAT_WS函式相結合，使查詢結果更有用。
例如，製作客戶分號分隔值列表：
- 首先，使用
CONCAT_WS函式連線每個客戶聯絡人的姓氏和名字，結果是聯絡人的全名。 - 然後，使用
GROUP_CONCAT函式來建立列表。
以下查詢使客戶的分號分隔值列表。
SELECT
GROUP_CONCAT(CONCAT_WS(', ', contactLastName, contactFirstName)
SEPARATOR ';')
FROM
customers;
請注意，GROUP_CONCAT函式將字串值連線在不同的行中，而CONCAT_WS或CONCAT函式將不同列中的兩個或多個字串值連線起來。
MySQL GROUP_CONCAT函式：常見錯誤
GROUP_CONCAT函式返回單個字串，而不是值列表。 這意味著您不能在IN操作符中使用GROUP_CONCAT函式的結果，例如在子查詢中使用。
例如，GROUP_CONCAT函式返回值的結果：1, 2和3連線成為字串：1,2,3 。
如果將此結果提供給IN運算子，則查詢不能正常工作。因此，查詢可能不返回任何結果。例如，以下查詢將無法正常工作。
因為IN運算子接受諸如(1,2,3)的值的列表，而不是由值列表('1,2,3')組成的字串。 因此，以下查詢將無法正常工作。
SELECT
id, name
FROM
table_name
WHERE
id IN GROUP_CONCAT(id);
因為GROUP_CONCAT函式是一個聚合函式，要對值進行排序，必須在函式內使用ORDER BY子句，而不是SELECT語句中的ORDER BY。
以下範例演示了在使用GROUP_CONCAT函式的上下文中ORDER BY子句的不正確使用：
SELECT
GROUP_CONCAT(DISTINCT country
SEPARATOR ';')
FROM
customers
ORDER BY country;
SELECT子句返回一個字串值，因此ORDER BY子句在此語句中不起作用。
MySQL GROUP_CONCAT應用程式
在許多情況下，您可以應用GROUP_CONCAT函式來產生有用的結果。 以下列表是使用GROUP_CONCAT函式的一些常見範例。
- 用逗號分隔的使用者角色，如「管理員，作者，編輯人員」。
- 生成逗號分隔的使用者的愛好，如「設計，程式設計，閱讀」。
- 為部落格貼文，文章或產品建立標籤，例如「mysql，mysql聚合函式，mysql教學」。
在本教學中，我們介紹了MySQL GROUP_CONCAT函式，將非空值從一組字串連線到具有各種選項的字串中。
參考
MySQL GROUP_CONCAT函式 :
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html