Declaration
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Description
Returns a string result containing non-NULL values from the connections of a group.
ORDER BY: the column or columns used for sorting.ASCindicates ascending order, andDESCindicates descending order. The default isASC.SEPARATOR: the separator character used to separate multiple connection strings. The default value 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 Graphic and Text Publishing House |
| 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 title 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
//Find book title information. The book title must be 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
// Query book titles and publishers. Group by book title and sort the results in descending order of publisher.
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 Graphic and Text Publishing House |
| Large-scale distributed storage system | China Machine Press |
+--------------------------------+---------------------------------------------------------------------+
4 rows in set