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示例

讓我們來看看示例資料庫(zaixiandb)中的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#function_group-concat