Application for MySQL Group_concat (connect the Val with the same ID with the character '/')

The application of MySQL Group_concat

Prepare beforehand:

CREATE TABLE Tbl_grp_cat (

ID INT,

VAL CHAR

);

INSERT into Tbl_grp_cat VALUES (1, ' a ');

INSERT into Tbl_grp_cat VALUES (1, ' B ');

INSERT into Tbl_grp_cat VALUES (1, ' C ');

INSERT into Tbl_grp_cat VALUES (2, ' d ');

INSERT into Tbl_grp_cat VALUES (2, ' e ');

INSERT into Tbl_grp_cat VALUES (2, ' d ');

INSERT into Tbl_grp_cat VALUES (2, ' f ');

Mysql> select * from Tbl_grp_cat;

+------+------+

| ID | VAL |

+------+------+

| 1 | A |

| 1 | B |

| 1 | C |

| 2 | D |

| 2 | e |

| 2 | D |

| 2 | f |

+------+------+

7 Rows in Set (0.00 sec)

Mysql>

Syntax

Group_concat ([DISTINCT] field to connect [order by Asc/desc sort field] [Separator ' delimiter '])

Example

Example 1: Connect Val with the same ID with a comma (can be repeated)

SELECT ID, Group_concat (val) Val from the Tbl_grp_cat group by ID of the ORDER by ID;

mysql> SELECT ID, Group_concat (val) Val from Tbl_grp_cat Group by ID ORDER by ID

;

+------+---------+

| ID | VAL |

+------+---------+

| 1 | A,b,c |

| 2 | d,e,d,f |

+------+---------+

2 rows in Set (0.00 sec)

Mysql>

Example 2: Connect Val with the same ID with commas (No duplicates)

The SELECT ID, Group_concat (DISTINCT val) Val from the Tbl_grp_cat group by ID of the ORDER by ID;

mysql> SELECT ID, Group_concat (DISTINCT val) Val from Tbl_grp_cat Group by ID OR

DER by ID;

+------+-------+

| ID | VAL |

+------+-------+

| 1 | A,b,c |

| 2 | d,e,f |

+------+-------+

2 rows in Set (0.00 sec)

Mysql>

Example 3: Connect the Val of the same ID from large to small with commas (can be repeated)

SELECT ID, Group_concat (val order by Val DESC) Val from the Tbl_grp_cat group by ID of the Order by ID;

Mysql> SELECT ID, Group_concat (Val Order by Val DESC) Val from Tbl_grp_cat Grou

By ID, ORDER by ID;

+------+---------+

| ID | VAL |

+------+---------+

| 1 | C,b,a |

| 2 | F,e,d,d |

+------+---------+

2 rows in Set (0.00 sec)

Mysql>

Example 4: Connect the Val with the same ID with the character '/' (can be repeated)

SELECT ID, Group_concat (val Separator '/') Val from Tbl_grp_cat the group by ID of the ORDER by ID;

Mysql> SELECT ID, Group_concat (Val Separator '/') Val from Tbl_grp_cat Group by

ID ORDER by ID;

+------+---------+

| ID | VAL |

+------+---------+

| 1 | a/b/c |

| 2 | d/e/d/f |

+------+---------+

2 rows in Set (0.00 sec)

Mysql>

Example 5: Right to use

SELECT ID, Group_concat (DISTINCT val Order by Val DESC Separator "/') Val from the Tbl_grp_cat group by ID of the order by ID;

Mysql> SELECT ID, Group_concat (DISTINCT val Order by Val DESC Separator '/') Val

From Tbl_grp_cat the GROUP by ID of the ORDER by ID;

+------+-------+

| ID | VAL |

+------+-------+

| 1 | c/b/a |

| 2 | f/e/d |

+------+-------+

2 rows in Set (0.00 sec)

Mysql>

Similar functions are available in Oracle (LISTAGG), Sqlite (GROUP_CONCAT), etc.

Application for MySQL Group_concat (connect the Val with the same ID with the character '/')