个人中心
退出
SQL 聚合函数之字符串分组合并
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
Oracle 中的 LISTAGG 函数
MySQL 中的 GROUP_CONCAT 函数
SQL Server 中的 STRING_AGG 函数
PostgreSQL 中的 STRING_AGG 函数
SQLite 中的 GROUP_CONCAT 函数
总结
大家好，我是只谈技术不剪发的 Tony 老师。
SQL 聚合函数（Aggregate Function）可以对一组数据进行汇总并返回单个结果。我们常见的聚合函数有 COUNT、AVG、SUM 等。今天给大家介绍一下如何通过 SQL 函数对字符串进行聚合，也就是将多行字符串合并成单个字符串。本文涉及的数据库包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。
如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
Oracle 中的 LISTAGG 函数
Oracle 实现了 SQL:2016 标准中的 LISTAGG 函数，例如（示例数据）以下查询返回了每个部门中的员工：
SELECT dept_id, LISTAGG(emp_name, ';')
FROM employee
GROUP BY dept_id;
DEPT_ID|LISTAGG(EMP_NAME,';') |
-------|-----------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;黄忠;魏延 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;关平;赵氏;关兴;张苞;赵统;周仓;马岱|
5|法正;庞统;蒋琬;黄权;糜竺;邓芝;简雍;孙乾 |
查询将每个部门中的多个员工姓名合并成了一行数据，多个姓名之间使用分号进行分隔。
如果想要在合并之前将员工进行排序，可以使用 WITHIN GROUP 选项。例如：
SELECT dept_id, LISTAGG(emp_name, ';') WITHIN GROUP (ORDER BY hire_date, salary DESC)
FROM employee
GROUP BY dept_id;
DEPT_ID|LISTAGG(EMP_NAME,';')WITHINGROUP(ORDERBYHIRE_DATE,SALARYDESC) |
-------|---------------------------------------------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;魏延;黄忠 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;周仓;关平;关兴;赵氏;赵统;张苞;马岱 |
5|法正;庞统;蒋琬;黄权;糜竺;孙乾;邓芝;简雍 |
对于每个部门中的员工，首先按照入职日期进行排序，入职日期相同再按照月薪从高到低进行排序，然后将员工姓名进行合并。
除此之外，LISTAGG 函数还支持一些其他选项，完整的语法如下：
LISTAGG( [ ALL ] [ DISTINCT ] measure_expr [, 'delimiter'] [listagg_overflow_clause] )
[ WITHIN GROUP ] (order_by_clause) [OVER query_partition_clause]
listagg_overflow_clause::=
{ ON OVERFLOW ERROR }
|
{ ON OVERFLOW TRUNCATE [ 'truncation-indicator' ] [ { WITH | WITHOUT } COUNT ] }
其中，DISTINCT 表示排除每个分组中的重复数据，ALL（默认值）表示使用全部数据；默认的分隔符为 NULL；listagg_overflow_clause 用于处理合并结果超长的情况，ON OVERFLOW ERROR（默认值）表示超长时返回错误，ON OVERFLOW TRUNCATE 表示超长时截断字符串；OVER 子句表示将该函数当作分析函数使用。
LISTAGG 函数忽略输入参数中的空值。如果想要了解 LISTAGG 函数的更多用法，可以参考 Oracle 官方文档。
MySQL 中的 GROUP_CONCAT 函数
MySQL 提供了聚合字符串的 GROUP_CONCAT 函数，例如以下查询返回了每个部门中的员工：
SELECT dept_id, GROUP_CONCAT(emp_name SEPARATOR ';')
FROM employee
GROUP BY dept_id;
dept_id|GROUP_CONCAT(emp_name SEPARATOR ';') |
-------|---------------------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;黄忠;魏延 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;关平;赵氏;关兴;张苞;赵统;周仓;马岱 |
5|法正;庞统;蒋琬;黄权;糜竺;邓芝;简雍;孙乾 |
查询将每个部门中的多个员工姓名合并成了一行数据，多个姓名之间使用分号进行分隔。
如果想要在合并之前将员工进行排序，可以使用 ORDER BY 选项。例如：
SELECT dept_id, GROUP_CONCAT(emp_name ORDER BY hire_date, salary DESC SEPARATOR ';')
FROM employee
GROUP BY dept_id;
dept_id|GROUP_CONCAT(emp_name ORDER BY hire_date, salary DESC SEPARATOR ';')|
-------|--------------------------------------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;魏延;黄忠 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;周仓;关平;关兴;赵氏;赵统;张苞;马岱 |
5|法正;庞统;蒋琬;黄权;糜竺;孙乾;邓芝;简雍 |
对于每个部门中的员工，首先按照入职日期进行排序，入职日期相同再按照月薪从高到低进行排序，然后将员工姓名进行合并。
除此之外，GROUP_CONCAT 函数还支持一些其他选项，完整的语法如下：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
其中，DISTINCT 表示排除每个分组中的重复数据，默认表示使用全部数据；ORDER BY 表示聚合之前先排序；SEPARATOR 子句表用于指定分隔符，默认为逗号。
GROUP_CONCAT 函数忽略输入参数中的空值。如果 GROUP_CONCAT 函数返回的结果超过系统变量 group_concat_max_len 设置的长度，将会截断合并后的字符串。更多信息可以参考 MySQL 官方文档。
SQL Server 中的 STRING_AGG 函数
SQL Server 2017 提供了字符串聚合函数：STRING_AGG。例如，以下查询返回了每个部门中的员工：
SELECT dept_id, STRING_AGG(emp_name, ';')
FROM employee
GROUP BY dept_id;
dept_id| |
-------|-----------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;黄忠;魏延 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;关平;赵氏;关兴;张苞;赵统;周仓;马岱|
5|法正;庞统;蒋琬;黄权;糜竺;邓芝;简雍;孙乾 |
查询将每个部门中的多个员工姓名合并成了一行数据，多个姓名之间使用分号进行分隔。
如果想要在合并之前将员工进行排序，可以使用 WITHIN GROUP 选项。例如：
SELECT dept_id, STRING_AGG(emp_name, ';') WITHIN GROUP (ORDER BY hire_date, salary DESC)
FROM employee
GROUP BY dept_id;
dept_id| |
-------|-----------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;魏延;黄忠 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;周仓;关平;关兴;赵氏;赵统;张苞;马岱|
5|法正;庞统;蒋琬;黄权;糜竺;孙乾;邓芝;简雍 |
对于每个部门中的员工，首先按照入职日期进行排序，入职日期相同再按照月薪从高到低进行排序，然后将员工姓名进行合并。
STRING_AGG 函数的完整语法如下：
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
STRING_AGG 函数忽略输入参数中的空值。如果想要了解 STRING_AGG 函数的更多用法，可以参考 SQL Server 官方文档。
PostgreSQL 中的 STRING_AGG 函数
PostgreSQL 实现了字符串聚合的 STRING_AGG 函数。例如，以下查询返回了每个部门中的员工：
SELECT dept_id, STRING_AGG(emp_name, ';')
FROM employee
GROUP BY dept_id;
dept_id|string_agg |
-------|-----------------------------------------|
4|赵云;廖化;关平;赵氏;关兴;张苞;赵统;周仓;马岱|
2|诸葛亮;黄忠;魏延 |
3|孙尚香;孙丫鬟 |
1|刘备;关羽;张飞 |
5|法正;庞统;蒋琬;黄权;糜竺;邓芝;简雍;孙乾 |
查询将每个部门中的多个员工姓名合并成了一行数据，多个姓名之间使用分号进行分隔。
如果想要在合并之前将员工进行排序，可以使用 ORDER BY 选项。例如：
SELECT dept_id, STRING_AGG(emp_name, ';' ORDER BY hire_date, salary DESC)
FROM employee
GROUP BY dept_id;
dept_id|string_agg |
-------|-----------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;魏延;黄忠 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;周仓;关平;关兴;赵氏;赵统;张苞;马岱|
5|法正;庞统;蒋琬;黄权;糜竺;孙乾;邓芝;简雍 |
对于每个部门中的员工，首先按照入职日期进行排序，入职日期相同再按照月薪从高到低进行排序，然后将员工姓名进行合并。
STRING_AGG 函数的完整语法如下：
STRING_AGG ( [ALL|DISTINCT] expression, separator [ORDER BY …]) [ FILTER ( WHERE filter_clause ) ]
其中，DISTINCT 表示排除每个分组中的重复数据，ALL（默认值）表示使用全部数据；FILTER 子句用于指定一个过滤条件，只有满足条件的数据才会进行聚合。STRING_AGG 函数忽略输入参数中的空值。
关于聚合函数的详细介绍，可以参考 PostgreSQL 官方文档。
SQLite 中的 GROUP_CONCAT 函数
SQLite 提供了实现字符串聚合的GROUP_CONCAT 函数。例如，以下查询返回了每个部门中的员工：
SELECT dept_id, GROUP_CONCAT(emp_name, ';')
FROM employee
GROUP BY dept_id;
dept_id|GROUP_CONCAT(emp_name, ';') |
-------|------------------------------------------|
1|刘备;关羽;张飞 |
2|诸葛亮;黄忠;魏延 |
3|孙尚香;孙丫鬟 |
4|赵云;廖化;关平;赵氏;关兴;张苞;赵统;周仓;马岱 |
5|法正;庞统;蒋琬;黄权;糜竺;邓芝;简雍;孙乾 |
查询将每个部门中的多个员工姓名合并成了一行数据，多个姓名之间使用分号进行分隔。
如果不指定分隔符，GROUP_CONCAT 函数默认使用逗号进行分隔。GROUP_CONCAT 函数忽略输入参数中的空值。同时，该函数不支持分组内的数据排序，按照随意顺序进行字符串合并。
总结
本文介绍了 5 种主流数据库中的字符串聚合函数，使用时需要注意不同数据库中的参数差异。
分类导航