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 种主流数据库中的字符串聚合函数,使用时需要注意不同数据库中的参数差异。