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. By default, no separator is used.
Examples
obclient> SELECT * FROM book; // Return all columns (bookid, bookname, and publishname) from the 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
// Search for 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
// Search for 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
// Search for book names and publishers, group the results by book name, and sort the results 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
