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, a 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