摘要:在本教程中,您将学习如何使用 MySQL GROUP_CONCAT()
函数通过各种选项连接组中的字符串。
MySQL GROUP_CONCAT()
函数简介
MySQL GROUP_CONCAT()
函数是一个聚合函数,它将一组字符串连接成具有各种选项的单个字符串。
下面显示了GROUP_CONCAT()
函数的语法:
GROUP_CONCAT(
DISTINCT expression
ORDER BY expression
SEPARATOR sep
);
Code language: SQL (Structured Query Language) (sql)
以下示例演示了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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
但是,有些客户位于同一国家/地区。要删除重复的国家/地区名称,请添加DISTINCT
子句作为以下查询:
SELECT
GROUP_CONCAT(DISTINCT country)
FROM
customers;
Code language: SQL (Structured Query Language) (sql)
如果国家/地区的名称按升序排列,则更易于阅读。要在连接之前对国家/地区名称进行排序,请使用ORDER BY
子句,如下所示:
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country)
FROM
customers;
Code language: SQL (Structured Query Language) (sql)
要将返回字符串的默认分隔符从逗号 (,) 更改为分号 (;),请使用SEPARATOR
子句作为以下查询:
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country
SEPARATOR ';')
FROM
customers;
Code language: SQL (Structured Query Language) (sql)
伟大的!现在您知道GROUP_CONCAT()
函数是如何工作的了。让我们举一个实际的例子。
每个客户都有一名或多名销售代表。换句话说,每个销售人员负责一个或多个客户。要找出谁负责哪些客户,可以使用内连接子句,如下所示:
SELECT
employeeNumber,
firstname,
lastname,
customername
FROM
employees
INNER JOIN
customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY
firstname,
lastname;
Code language: SQL (Structured Query Language) (sql)
现在,我们可以按员工编号对结果集进行分组,并使用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;
Code language: SQL (Structured Query Language) (sql)
结果集更容易阅读。
使用 MySQL GROUP_CONCAT()
和CONCAT_WS()
函数示例
有时, GROUP_CONCAT
函数可以与CONCAT_WS函数结合使用,以使查询结果更有用。
例如,要制作以分号分隔的客户值列表:
- 首先,使用
CONCAT_WS()
函数连接每个客户联系人的姓氏和名字。结果是联系人的全名。 - 然后,您使用
GROUP_CONCAT()
函数来创建列表。
以下查询创建一个以分号分隔的客户值列表。
SELECT
GROUP_CONCAT(
CONCAT_WS(', ', contactLastName, contactFirstName)
SEPARATOR ';')
FROM
customers;
Code language: SQL (Structured Query Language) (sql)
请注意, GROUP_CONCAT()
函数连接不同行中的字符串值,而CONCAT_WS()
或CONCAT()
函数连接不同列中的两个或多个字符串值。
MySQL GROUP_CONCAT
函数:常见错误
GROUP_CONCAT()
函数返回单个字符串,而不是值列表。这意味着您不能将GROUP_CONCAT()
函数的结果用于IN运算符,例如在子查询中。
例如, 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);
Code language: SQL (Structured Query Language) (sql)
由于GROUP_CONCAT
函数是聚合函数,因此要对值进行排序,必须在函数内部使用ORDER BY
子句,而不是在SELECT语句中的ORDER BY
中。
以下示例演示了在使用GROUP_CONCAT
函数的上下文中ORDER BY
子句的错误用法:
SELECT
GROUP_CONCAT(DISTINCT country
SEPARATOR ';')
FROM
customers
ORDER BY country;
Code language: SQL (Structured Query Language) (sql)
SELECT
子句返回一个字符串值,因此ORDER BY
子句在此语句中不起作用。
MySQL GROUP_CONCAT()
函数应用
在很多情况下,您可以应用GROUP_CONCAT()
函数来产生有用的结果。以下列表是使用GROUP_CONCAT()
函数的一些常见示例。
- 设置以逗号分隔的用户角色,例如“管理员、作者、编辑者”。
- 产生以逗号分隔的用户爱好,例如“设计、编程、阅读”。
- 为博客文章、文章或产品创建标签,例如“mysql、mysql 聚合函数、mysql 教程”。
在本教程中,您学习了如何使用 MySQL GROUP_CONCAT()
函数将一组字符串的非NULL
值连接成单个字符串。