Group_concat function usage in MySQL

The function returns a string result with a non-null value for a connection from a group. The function is an enhanced Sybase SQL anywhere supported basic list () function.

Syntax structure:

Group_concat ([DISTINCT] expr [, expr ...] [ORDER by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name ...] [SEPARATOR Str_val])

DISTINCT: Removing duplicate values

expr [, expr ...] : One or more fields (or expressions)

ORDER by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name ...] : Sort by field or expression, which can be multiple

SEPARATOR str_val: Delimiter (comma by default)

Example 1: querying all subcategories of a taxonomy and connecting the subcategory ID with commas

Mysql>select Group_concat (cat_id) from goods_cat WHERE pid = 25+-----------------------------+| Group_concat (cat_id)        |+-----------------------------+| 26,111,130,206,239,322,323  |+------------------- ----------+

　　

Example 2: querying all subcategories of a taxonomy and connecting the subcategory ID with semicolons

Mysql>select group_concat (cat_id SEPARATOR '; ') From goods_cat WHERE pid = 25+-------------------------------------+| Group_concat (cat_id SEPARATOR '; ')  | +-------------------------------------+| 26;111;130;206;239;322;323          |+-------------------------------------+

　　

Example 3: querying all subcategories of a taxonomy, according to P_order ASC, cat_id desc sort and then connect

Mysql>select Group_concat (cat_id ORDER by P_order ASC, cat_id DESC) from goods_cat WHERE pid = 25+--------------------- -------------------------------------+| Group_concat (cat_id ORDER by P_order ASC, cat_id DESC)   |+------------------------------------------------------- ---+| 332,331,242,212,133,112,29,26,333,330,327,244,138,116    |+--------------------------------------------------- -------+

Example 4: combining GROUP BY queries

Mysql>select pid, Group_concat (cat_id) from Goods_cat GROUP by pid+-----------+----------------------------------- --+| parent_id | Group_concat (cat_id)                |+-----------+-------------------------------------+|        22 | 35,166,191,209,233,252,256,257,258  | |        25 | 26,111,130,206,239,322,323          | |        26 | 29,51,65,66,70,75,238               | |       323 | 332,333,334,335,336,337,338,339     |+-----------+-------------------------------------+

　　

Attention:

1. Maximum length (character) limit

System variables: Group_concat_max_len

SET [SESSION | GLOBAL] Group_concat_max_len = val;

　　

Val must be an unsigned integer

With the Group_concat function, the limit statement in the SELECT statement does not serve any purpose.

2. int type trap

When a concatenated field is of type int, the lower version or the result returned is not a comma-delimited string, but byte[].

At this point, you need to convert with the cast or convert function.

Group_concat function usage in MySQL