当前位置: 首页>>代码示例>>用法及示例精选>>正文


MySQL Group_CONCAT()用法及代码示例

MySQL中的GROUP_CONCAT()函数用于将多行数据合并为一个字段。这是一个汇总(GROUP BY)函数,如果该组包含至少一个非NULL值,则该函数返回String值。否则,它返回NULL。

用法:

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.

在GROUP_CONCAT()函数内部使用各种子句:


  • Dinstinct:它消除了结果中值的重复。
  • Order By:它按特定顺序对组的值进行排序,然后将它们连接起来。
  • Separator:默认情况下,group的值由(,)运算符分隔。为了更改此分隔符值,请使用Separator子句,后跟字符串文字。它以分隔符“ str_value”的形式给出。

例子:

让我们考虑一个“Employee”表:

emp_id 名字 dept_id 强度
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

查询:

  1. 使用简单的GROUP_CONCAT()函数-
    SELECT emp_id, fname, lname, dept_id, 
    GROUP_CONCAT ( strength ) as "strengths" 
    FROM employee group by emp_id;
    

    输出:

    emp_id 名字 dept_id 长处
    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. 使用DISTINCT子句-
    SELECT dept_id, 
    GROUP_CONCAT ( DISTINCT strength) 
    as "employees strengths"  
    from employee group by dept_id;
    

    输出:


    dept_id 员工优势
    1 Goal-oriented, Hard-working
    2 Leadership, Punctuality, Quick-learner, Responsible
    3 Critical thinking, Hard-working, Listening, Self-motivated

  3. 使用ORDER BY子句:
    SELECT dept_id, 
    GROUP_CONCAT ( DISTINCT emp_id ORDER BY emp_id  SEPARATOR', ') 
    as "employees ids" 
    from employee group by dept_id;
    

    在这里,分隔符','将用逗号(,)和空格字符分隔值。

    输出:

    dept_id 员工编号
    1 5
    2 1, 2
    3 3, 4

如何在单个字段中连接不同列的多行。

到现在为止,我们已经看到使用GROUP_CONCAT()函数将属于同一列的多行的值进行分组。但是,结合使用concat()函数和group_concat()函数,我们可以将不止行的多个列值组合到单个字段中。

例:

考虑到上表“employee”,如果我们希望在第二个查询中找到员工实力和员工ID,则将其写为-

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;

说明:


上面的查询由两个SELECT语句组成,一个是内部的,另一个是外部的。

内部SELECT语句-

SELECT dept_id, concat ( emp_id, ':',
GROUP_CONCAT ( strength separator ', ' ) ) as "strengths"  
FROM employee GROUP BY emp_id

它将根据“emp_id”对employee表的行进行分组。结果的第一列显示dept_id,第二列显示emp_id及其强度列表。

输出对于内部SELECT语句-

dept_id 长处
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

现在,外部SELECT语句将根据“dept_id”将这些行分组。

输出:

dept_id emp-id:优点
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

注意:GROUP_CONCAT()函数的结果被截断为最大长度,即1024,这由系统变量group_concat_max_len给出。但是,可以在运行时通过使用SETcommand as-来更改group_concat_max_len变量的值-

SET [GLOBAL | SESSION] group_concat_max_len = value;

value: It is the new value set to the variable.


相关用法

注:本文由纯净天空筛选整理自Tanvi_Garg大神的英文原创作品 MySQL | Group_CONCAT() Function。非经特殊声明,原始代码版权归原作者所有,本译文的传播和使用请遵循“署名-相同方式共享 4.0 国际 (CC BY-SA 4.0)”协议。