Syntax
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Purpose
GROUP_CONCAT() returns a string of concatenated non-NULL values from a group.
ORDER BYspecifies a column for sorting values. To sort values in ascending order, specifyASC. To sort values in descending order, specifyDESC. By default,ASCis used.SEPARATORspecifies the separator between strings. The default separator is a comma (,).
Examples
obclient> SELECT * FROM book; // Return all columns (bookid, bookname, and publishname) from Table book.
+--------+--------------------------------+-----------------------------+
| bookid | bookname | publishname |
+--------+--------------------------------+-----------------------------+
| 1 | Git Help | Alibaba Group Publisher |
| 2 | MySQL Performance Optimization | Zhejiang University Press |
| 3 | Java Programming Guide | China Machine Press |
| 3 | Java Programming Guide | China Machine Press |
| 4 | Large-Scale Distributed Storage System | China Machine Press |
+--------+--------------------------------+-----------------------------+
5 rows in set
// Return book names.
obclient> 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 System |
+-----------------------------------+
4 rows in set
// Return unique book names.
obclient> 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 System |
+----------------------------------+
4 rows in set
// Return book names and publishers, with the results grouped by book name and sorted by publisher in descending order.
obclient> 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 | China Machine Press; China Machine Press |
| MySQL Performance Optimization | Zhejiang University Press |
| Large-Scale Distributed Storage System | China Machine Press |
+--------------------------------+---------------------------------------------------------------------+
4 rows in set