SQL 聚合函数之字符串分组合并

文章目录

        Oracle 中的 LISTAGG 函数
        MySQL 中的 GROUP_CONCAT 函数
        SQL Server 中的 STRING_AGG 函数
        PostgreSQL 中的 STRING_AGG 函数
        SQLite 中的 GROUP_CONCAT 函数
        总结

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;

更多请见：http://www.mark-to-win.com/tutorial/51513.html