转自:https://www.geeksforgeeks.org/mysql-group_concat-function/
一、GROUP_CONCAT()函数
GROUP_CONCAT函数返回一个字符串结果,该结果由分组中的值连接组合而成。
1、使用语法及特点:
SELECT col1, col2, ..., colN
GROUP_CONCAT ( [DISTINCT] col_name1
[ORDER BY clause] [SEPARATOR str_val] )
FROM table_name GROUP BY col_name2;
col1, col2, ...colN : These are the column names of table.
col_name1: Column of the table whose values are concatenated into a single field for each group.
table_name: Name of table.
col_name2: Column of the table according to which grouping is done.
Separator: By default, the values of group are separated by (, ) operator. In order to change this separator value, Separator clause is used followed by a string literal. It is given as Separator ‘str_value’.
例1:Let, consider an “Employee” table:
原表如下:
emp_id | fname | lname | dept_id | strength |
---|---|---|---|---|
1 | mukesh | gupta | 2 | Leadership |
3 | neelam | sharma | 3 | Hard-working |
1 | mukesh | gupta | 2 | Responsible |
2 | devesh | tyagi | 2 | Punctuality |
3 | neelam | sharma | 3 | Self-motivated |
1 | mukesh | gupta | 2 | Quick-learner |
4 | keshav | singhal | 3 | Listening |
2 | devesh | tyagi | 2 | Quick-learner |
5 | tanya | jain | 1 | Hard-working |
4 | keshav | singhal | 3 | Critical thinking |
5 | tanya | jain | 1 | Goal-oriented |
SELECT emp_id, fname, lname, dept_id,
GROUP_CONCAT ( strength ) as "strengths"
FROM employee group by emp_id;
emp_id | fname | lname | dept_id | strengths |
---|---|---|---|---|
1 | mukesh | gupta | 2 | Leadership, Resposible, Quick-learner |
2 | devesh | tyagi | 2 | Punctuality, Quick-learner |
3 | neelam | sharma | 3 | Hard-working, Self-motivated |
4 | keshav | singhal | 3 | Listening, Critical thinking |
5 | tanya | jain | 1 | Hard-working, Goal-oriented |
例2:sing a DISTINCT clause-
SELECT dept_id,
GROUP_CONCAT ( DISTINCT strength)
as "employees strengths"
from employee group by dept_id;
dept_id | employees strengths |
---|---|
1 | Goal-oriented, Hard-working |
2 | Leadership, Punctuality, Quick-learner, Responsible |
3 | Critical thinking, Hard-working, Listening, Self-motivated |
例3:Use of ORDER BY clause:
SELECT dept_id,
GROUP_CONCAT ( DISTINCT emp_id ORDER BY emp_id SEPARATOR', ')
as "employees ids"
from employee group by dept_id;
Here, Separator ', ' will separate the values by a comma (, ) and a whitespace character.
Output:
dept_id | employees ids |
---|---|
1 | 5 |
2 | 1, 2 |
3 | 3, 4 |
三、连接单个字段中不同列的多行
How to concatenate multiple rows of different columns in a single field.
Til now we have seen the use of GROUP_CONCAT() function to group the values of multiple rows that belongs to same column. But, using concat() function and group_concat() function together, we can combine more than one column values of different rows into single field.
Considering above table “employee”, if we wish to find employees strength along with employees id in second query then it is written as-
SELECT dept_id, GROUP_CONCAT ( strengths SEPARATOR ' ') as "emp-id : strengths"
FROM ( SELECT dept_id, CONCAT ( emp_id, ':', GROUP_CONCATt(strength SEPARATOR', ') )
as "strengths" FROM employee GROUP BYy emp_id )as emp GROUP BY dept_id;
The above query consist of two SELECT statements an inner one and the outer one.
The inner SELECT statement –
SELECT dept_id, concat ( emp_id, ':',
GROUP_CONCAT ( strength separator ', ' ) ) as "strengths"
FROM employee GROUP BY emp_id
Output for inner SELECT statement-
dept_id | strengths |
---|---|
2 | 1: Leadership, Responsible, Quick-learner |
2 | 2: Punctuality, Quick-learner |
3 | 3: Hard-working, Self-motivated |
3 | 4: Listening, Critical thinking |
1 | 5: Hard-working, Goal-oriented |
The outer SELECT statement will now group these rows according to “dept_id”.
Output:
dept_id | emp-id : strengths |
---|---|
1 | 5: Hard-working, Goal-oriented |
2 | 1: Leadership, Responsible, Quick-learner 2:Punctuality, Quick-learner |
3 | 3: Hard-working, Self-motivated 4:Listening, Critical thinking |
Note: The result of GROUP_CONCAT() function is truncated to the maximum length i.e 1024 which is given by sytem variable group_concat_max_len. However, the value of group_concat_max_len variable can be changed at runtime by using SETcommand as-
SET [GLOBAL | SESSION] group_concat_max_len = value;
value: It is the new value set to the variable.