GROUP_CONCAT

Syntax

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

Description

Returns a string result containing non-NULL values from a group.

• ORDER BY specifies the sorting column. ASC indicates ascending order, and DESC indicates descending order. The default is ASC.

• SEPARATOR specifies the delimiter between concatenated strings. The default delimiter is ,.

Examples

SELECT * FROM book;     //book table (book ID, book name, publisher)
+--------+--------------------------------+-----------------------------+
| bookid | bookname                       | publishname                 |
+--------+--------------------------------+-----------------------------+
|      1 | git help                       | alibaba group publisher     |
|      2 | MySQL Performance Optimization | Zhejiang University Press   |
|      3 | Java Programming Guide          | Machinery Industry Press    |
|      3 | Java Programming Guide          | Machinery Industry Press    |
|      4 | Large-Scale Distributed Storage Systems | Machinery Industry Press |
+--------+--------------------------------+-----------------------------+
5 rows in set (0.001 sec) (0.003 sec)

//Query book name information
SELECT GROUP_CONCAT(bookname) FROM book GROUP BY bookname;
+-----------------------------------+
| GROUP_CONCAT(bookname)            |
+-----------------------------------+
| git help                          |
| Java Programming Guide,Java Programming Guide         |
| MySQL Performance Optimization                     |
| Large-Scale Distributed Storage Systems              |
+-----------------------------------+
4 rows in set (0.001 sec) (0.003 sec)

//Query book name information with unique book names
SELECT GROUP_CONCAT(distinct(bookname)) FROM book GROUP BY bookname;
+----------------------------------+
| GROUP_CONCAT(distinct(bookname)) |
+----------------------------------+
| git help                         |
| Java Programming Guide                     |
| MySQL Performance Optimization                    |
| Large-Scale Distributed Storage Systems             |
+----------------------------------+
4 rows in set (0.001 sec) (0.003 sec)

//Query book name and publisher information, grouped by book name and sorted in descending order by publisher
SELECT bookname, GROUP_CONCAT(publishname ORDER BY publishname DESC SEPARATOR ';' ) FROM book GROUP BY bookname;
+--------------------------------+---------------------------------------------------------------------+
| bookname                       | GROUP_CONCAT(publishname ORDER BY publishname DESC SEPARATOR ';' ) |
+--------------------------------+---------------------------------------------------------------------+
| git help                       | alibaba group publisher                                             |
| Java Programming Guide                   | Machinery Industry Press;Machinery Industry Press                                       |
| MySQL Performance Optimization                  | Zhejiang University Press                                                  |
| Large-Scale Distributed Storage Systems           | Machinery Industry Press                                                      |
+--------------------------------+---------------------------------------------------------------------+
4 rows in set (0.003 sec)