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; // Table book (book ID, book name, and publisher).
+--------+--------------------------------+-----------------------------+
| bookid | bookname | publishername |
+--------+--------------------------------+-----------------------------+
| 1 | GiT Help | Alibaba Group |
| 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 name
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 the book name. The book name is unique.
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 the book name and publisher information, group the information by book name, and display the publisher information 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 |
| 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