MySQL GROUP_CONCAT函数

时间:2019-11-20 08:52:16  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用MySQL GROUP_CONCAT()函数将带有各种选项的组中的字符串连接起来。

MySQL GROUP_CONCAT()函数简介

MySQL GROUP_CONCAT()函数是一个聚合函数,可将组中的字符串连接成具有各种选项的单个字符串。

下面显示了GROUP_CONCAT()函数的语法:

GROUP_CONCAT(
    DISTINCT expression
    ORDER BY expression
    SEPARATOR sep
);

下面的示例演示GROUP_CONCAT()函数的工作方式。

CREATE TABLE t (
    v CHAR
);

INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');

SELECT 
    GROUP_CONCAT(DISTINCT v
        ORDER BY v ASC
        SEPARATOR ';')
FROM
    t;

DISTINCT条款可让您在串联之前消除组中的重复值。

ORDER BY子句允许您在连接之前按升序或降序对值进行排序。
默认情况下,它将按升序对值进行排序。
如果要按降序对值进行排序,则需要显式指定DESC选项。

SEPARATOR指定在组中的值之间插入的文字值。
如果未指定分隔符,GROUP_CONCAT函数将使用逗号(,)作为默认分隔符。

GROUP_CONCAT函数将忽略NULL值。
如果找不到匹配的行或所有参数均为NULL值,则返回NULL。

GROUP_CONCAT函数返回一个二进制或非二进制字符串,该字符串取决于参数。
默认情况下,返回字符串的最大长度为1024。
如果您需要更多,可以通过将SESSION或GLOBAL级别的group_concat_max_len系统变量设置为最大长度来扩展最大长度。

MySQL GROUP_CONCAT()函数示例

让我们看一下示例数据库中的customers表。

要使用逗号分隔的字符串获取客户所在的所有国家/地区,请使用GROUP_CONCAT()函数,如下所示:

SELECT 
    GROUP_CONCAT(country)
FROM
    customers;

但是,某些客户位于同一国家/地区。
要删除重复的国家/地区名称,请在以下查询中添加DISTINCT子句:

SELECT 
    GROUP_CONCAT(DISTINCT country)
FROM
    customers;

如果国家/地区的名称按升序排列,则可读性更高。
要在连接前对国家/地区名称进行排序,请使用ORDER BY子句,如下所示:

SELECT 
    GROUP_CONCAT(DISTINCT country
        ORDER BY country)
FROM
    customers;

要将返回的字符串的默认分隔符从逗号(,)更改为分号(;),请使用SEPARATOR子句作为以下查询:

SELECT 
    GROUP_CONCAT(DISTINCT country
        ORDER BY country
        SEPARATOR ';')
FROM
    customers;

大!现在您知道GROUP_CONCAT()函数的工作原理。
让我们举一个实际的例子。

每个客户都有一个或多个销售代表。
换句话说,每个销售员工负责一个或多个客户。
要确定谁负责哪个客户,可以使用内部join子句,如下所示:

SELECT 
    employeeNumber, 
    firstname, 
    lastname, 
    customername
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY 
	firstname, 
    lastname;

现在,我们可以按员工编号对结果集进行分组,并使用GROUP_CONCAT()函数将负责该员工的所有员工连接起来,如下所示:

SELECT 
    employeeNumber,
    firstName,
    lastName,
    GROUP_CONCAT(DISTINCT customername
        ORDER BY customerName)
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;

结果集更容易阅读。

结合使用MySQL GROUP_CONCAT()和CONCAT_WS()函数示例

有时,可以将GROUP_CONCAT函数与CONCAT_WS函数结合使用,以使查询结果更加有用。

例如,列出客户的分号分隔值:

  • 首先,使用CONCAT_WS()函数将每个客户联系人的姓氏和名字串联起来。
    结果是联系人的全名。

  • 然后,使用GROUP_CONCAT()函数创建列表。

以下查询列出客户的分号分隔值。

SELECT 
    GROUP_CONCAT(
       CONCAT_WS(', ', contactLastName, contactFirstName)
       SEPARATOR ';')
FROM
    customers;

请注意,GROUP_CONCAT()函数可将不同行中的字符串值连接在一起,而CONCAT_WS()或CONCAT()函数可将不同列中的两个或多个字符串值连接在一起。

MySQL GROUP_CONCAT函数:常见错误

GROUP_CONCAT()函数返回单个字符串,而不是值列表。
这意味着您不能在IN运算符中使用GROUP_CONCAT()函数的结果,例如,在子查询中。

例如,GROUP_CONCAT()函数返回值的结果:1、2和3作为" 1、2、3"字符串。

如果将此结果提供给IN运算符,则该查询不起作用。
因此,查询可能不会返回任何结果。
例如,以下查询将无法正常工作。

因为IN运算符接受值列表,例如(1,2,3),而不是包含值列表('1,2,3')的字符串。
因此,以下查询将无法正常工作。

SELECT 
    id, name
FROM
    table_name
WHERE
    id IN GROUP_CONCAT(id);

由于GROUP_CONCAT函数是聚合函数,因此要对值进行排序,必须在函数内部而不是SELECT语句的ORDER BY中使用ORDER BY子句。

下面的示例演示了在使用GROUP_CONCAT函数的上下文中ORDER BY子句的错误用法:

SELECT 
    GROUP_CONCAT(DISTINCT country
        SEPARATOR ';')
FROM
    customers
ORDER BY country;

SELECT子句返回一个字符串值,因此ORDER BY子句在此语句中不起作用。

MySQL GROUP_CONCAT()函数应用程序

在许多情况下,您可以应用GROUP_CONCAT()函数产生有用的结果。
以下列表是使用GROUP_CONCAT()函数的一些常见示例。

  • 设置以逗号分隔的用户角色,例如"管理员,作者,编辑者"。

  • 产生以逗号分隔的用户爱好,例如"设计,编程,阅读"。

  • 为博客文章,文章或产品创建标签,例如" mysql,mysql聚合函数,mysql教程"。

在本教程中,您学习了如何使用MySQL GROUP_CONCAT()函数将一组字符串的非NULL值连接为单个字符串。