Syntax
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Purpose
Returns a string result containing non-NULL values from a group, connected by a separator.
ORDER BYspecifies the sorting column,ASCindicates ascending order, andDESCindicates descending order. The default isASC.SEPARATORspecifies the separator between concatenated strings. The default separator is,.
Examples
obclient> SELECT * FROM book; // Table book (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 System | Machinery Industry Press |
+--------+--------------------------------+-----------------------------+
5 rows in set
// Query book name information
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
// Query book name information, ensuring uniqueness
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
// Query book name and publisher information, grouping by book name and sorting publishers 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 | Machinery Industry Press;Machinery Industry Press |
| MySQL Performance Optimization | Zhejiang University Press |
| Large-Scale Distributed Storage System | Machinery Industry Press |
+--------------------------------+---------------------------------------------------------------------+
4 rows in set