Use of GROUP_CONCAT

GROUP_CONCAT

GROUP_CONCAT is used together with group by to concatenate values in a column with a specified separator. The default MySQL separator is all good. The syntax is as follows:

group concat([distinct] column_name [order by column_name asc/desc] [separator 'separator'])
Copy the code

Such as:

1. Get the total number and personnel of each branch

select dept, count(*) ,group_concat(name) from employee group by dept;
Copy the code

2. Get the total number and personnel of each branch, personnel sorted by name

select dept, count(*) ,group_concat(name order by name desc) from employee group by dept;
Copy the code

3. Gets the total number of people and staff for each branch, separated by a colon

select dept, count(*) ,group_concat(name order by name descThe separator,':') from employee group by dept;
Copy the code