SQL 聚合函數(shù)之字符串分組合并

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        Oracle 中的 LISTAGG 函數(shù)
        MySQL 中的 GROUP_CONCAT 函數(shù)
        SQL Server 中的 STRING_AGG 函數(shù)
        PostgreSQL 中的 STRING_AGG 函數(shù)
        SQLite 中的 GROUP_CONCAT 函數(shù)
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

SQL 聚合函數(shù)（Aggregate Function）可以對一組數(shù)據(jù)進(jìn)行匯總并返回單個結(jié)果。我們常見的聚合函數(shù)有 COUNT、AVG、SUM 等。今天給大家介紹一下如何通過 SQL 函數(shù)對字符串進(jìn)行聚合，也就是將多行字符串合并成單個字符串。本文涉及的數(shù)據(jù)庫包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。

如果覺得文章有用，歡迎評論??、點(diǎn)贊??、推薦??
Oracle 中的 LISTAGG 函數(shù)

Oracle 實現(xiàn)了 SQL:2016 標(biāo)準(zhǔn)中的 LISTAGG 函數(shù)，例如（示例數(shù)據(jù)）以下查詢返回了每個部門中的員工：

SELECT dept_id, LISTAGG(emp_name, ';')
FROM employee
GROUP BY dept_id;

DEPT_ID|LISTAGG(EMP_NAME,';')                    |
-------|-----------------------------------------|
      1|劉備;關(guān)羽;張飛                            |
      2|諸葛亮;黃忠;魏延                          |
      3|孫尚香;孫丫鬟                             |
      4|趙云;廖化;關(guān)平;趙氏;關(guān)興;張苞;趙統(tǒng);周倉;馬岱|
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;鄧芝;簡雍;孫乾    |



查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進(jìn)行分隔。

如果想要在合并之前將員工進(jìn)行排序，可以使用 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|劉備;關(guān)羽;張飛                                                              |
      2|諸葛亮;魏延;黃忠                                                            |
      3|孫尚香;孫丫鬟                                                               |
      4|趙云;廖化;周倉;關(guān)平;關(guān)興;趙氏;趙統(tǒng);張苞;馬岱                                  |
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;孫乾;鄧芝;簡雍                                      |



對于每個部門中的員工，首先按照入職日期進(jìn)行排序，入職日期相同再按照月薪從高到低進(jìn)行排序，然后將員工姓名進(jìn)行合并。

除此之外，LISTAGG 函數(shù)還支持一些其他選項，完整的語法如下：

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 表示排除每個分組中的重復(fù)數(shù)據(jù)，ALL（默認(rèn)值）表示使用全部數(shù)據(jù)；默認(rèn)的分隔符為 NULL；listagg_overflow_clause 用于處理合并結(jié)果超長的情況，ON OVERFLOW ERROR（默認(rèn)值）表示超長時返回錯誤，ON OVERFLOW TRUNCATE 表示超長時截斷字符串；OVER 子句表示將該函數(shù)當(dāng)作分析函數(shù)使用。

LISTAGG 函數(shù)忽略輸入?yún)?shù)中的空值。如果想要了解 LISTAGG 函數(shù)的更多用法，可以參考 Oracle 官方文檔。
MySQL 中的 GROUP_CONCAT 函數(shù)

MySQL 提供了聚合字符串的 GROUP_CONCAT 函數(shù)，例如以下查詢返回了每個部門中的員工：

SELECT dept_id, GROUP_CONCAT(emp_name SEPARATOR ';')
FROM employee
GROUP BY dept_id;

dept_id|GROUP_CONCAT(emp_name SEPARATOR ';')               |
-------|---------------------------------------------------|
      1|劉備;關(guān)羽;張飛                                      |
      2|諸葛亮;黃忠;魏延                                    |
      3|孫尚香;孫丫鬟                                       |
      4|趙云;廖化;關(guān)平;趙氏;關(guān)興;張苞;趙統(tǒng);周倉;馬岱          |
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;鄧芝;簡雍;孫乾              |



查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進(jìn)行分隔。

如果想要在合并之前將員工進(jìn)行排序，可以使用 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|劉備;關(guān)羽;張飛                                                       |
      2|諸葛亮;魏延;黃忠                                                     |
      3|孫尚香;孫丫鬟                                                        |
      4|趙云;廖化;周倉;關(guān)平;關(guān)興;趙氏;趙統(tǒng);張苞;馬岱                          |
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;孫乾;鄧芝;簡雍                               |



對于每個部門中的員工，首先按照入職日期進(jìn)行排序，入職日期相同再按照月薪從高到低進(jìn)行排序，然后將員工姓名進(jìn)行合并。

除此之外，GROUP_CONCAT 函數(shù)還支持一些其他選項，完整的語法如下：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])


其中，DISTINCT 表示排除每個分組中的重復(fù)數(shù)據(jù)，默認(rèn)表示使用全部數(shù)據(jù)；ORDER BY 表示聚合之前先排序；SEPARATOR 子句表用于指定分隔符，默認(rèn)為逗號。

GROUP_CONCAT 函數(shù)忽略輸入?yún)?shù)中的空值。如果 GROUP_CONCAT 函數(shù)返回的結(jié)果超過系統(tǒng)變量 group_concat_max_len 設(shè)置的長度，將會截斷合并后的字符串。更多信息可以參考 MySQL 官方文檔。
SQL Server 中的 STRING_AGG 函數(shù)

SQL Server 2017 提供了字符串聚合函數(shù)：STRING_AGG。例如，以下查詢返回了每個部門中的員工：

SELECT dept_id, STRING_AGG(emp_name, ';')
FROM employee
GROUP BY dept_id;

dept_id|                                         |
-------|-----------------------------------------|
      1|劉備;關(guān)羽;張飛                            |
      2|諸葛亮;黃忠;魏延                          |
      3|孫尚香;孫丫鬟                             |
      4|趙云;廖化;關(guān)平;趙氏;關(guān)興;張苞;趙統(tǒng);周倉;馬岱|
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;鄧芝;簡雍;孫乾     |



查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進(jìn)行分隔。

如果想要在合并之前將員工進(jìn)行排序，可以使用 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|劉備;關(guān)羽;張飛                            |
      2|諸葛亮;魏延;黃忠                          |
      3|孫尚香;孫丫鬟                             |
      4|趙云;廖化;周倉;關(guān)平;關(guān)興;趙氏;趙統(tǒng);張苞;馬岱|
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;孫乾;鄧芝;簡雍     |



對于每個部門中的員工，首先按照入職日期進(jìn)行排序，入職日期相同再按照月薪從高到低進(jìn)行排序，然后將員工姓名進(jìn)行合并。

STRING_AGG 函數(shù)的完整語法如下：

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )



STRING_AGG 函數(shù)忽略輸入?yún)?shù)中的空值。如果想要了解 STRING_AGG 函數(shù)的更多用法，可以參考 SQL Server 官方文檔。
PostgreSQL 中的 STRING_AGG 函數(shù)

PostgreSQL 實現(xiàn)了字符串聚合的 STRING_AGG 函數(shù)。例如，以下查詢返回了每個部門中的員工：

SELECT dept_id, STRING_AGG(emp_name, ';')
FROM employee
GROUP BY dept_id;

dept_id|string_agg                               |
-------|-----------------------------------------|
      4|趙云;廖化;關(guān)平;趙氏;關(guān)興;張苞;趙統(tǒng);周倉;馬岱|
      2|諸葛亮;黃忠;魏延                          |
      3|孫尚香;孫丫鬟                             |
      1|劉備;關(guān)羽;張飛                            |
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;鄧芝;簡雍;孫乾     |



查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進(jìn)行分隔。

如果想要在合并之前將員工進(jìn)行排序，可以使用 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|劉備;關(guān)羽;張飛                            |
      2|諸葛亮;魏延;黃忠                          |
      3|孫尚香;孫丫鬟                             |
      4|趙云;廖化;周倉;關(guān)平;關(guān)興;趙氏;趙統(tǒng);張苞;馬岱|
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;孫乾;鄧芝;簡雍     |


對于每個部門中的員工，首先按照入職日期進(jìn)行排序，入職日期相同再按照月薪從高到低進(jìn)行排序，然后將員工姓名進(jìn)行合并。

STRING_AGG 函數(shù)的完整語法如下：

STRING_AGG ( [ALL|DISTINCT] expression, separator [ORDER BY …]) [ FILTER ( WHERE filter_clause ) ]



其中，DISTINCT 表示排除每個分組中的重復(fù)數(shù)據(jù)，ALL（默認(rèn)值）表示使用全部數(shù)據(jù)；FILTER 子句用于指定一個過濾條件，只有滿足條件的數(shù)據(jù)才會進(jìn)行聚合。STRING_AGG 函數(shù)忽略輸入?yún)?shù)中的空值。

關(guān)于聚合函數(shù)的詳細(xì)介紹，可以參考 PostgreSQL 官方文檔。
SQLite 中的 GROUP_CONCAT 函數(shù)

SQLite 提供了實現(xiàn)字符串聚合的GROUP_CONCAT 函數(shù)。例如，以下查詢返回了每個部門中的員工：

SELECT dept_id, GROUP_CONCAT(emp_name, ';')
FROM employee
GROUP BY dept_id;

dept_id|GROUP_CONCAT(emp_name, ';')               |
-------|------------------------------------------|
      1|劉備;關(guān)羽;張飛                             |
      2|諸葛亮;黃忠;魏延                           |
      3|孫尚香;孫丫鬟                              |
      4|趙云;廖化;關(guān)平;趙氏;關(guān)興;張苞;趙統(tǒng);周倉;馬岱 |
      5|法正;龐統(tǒng);蔣琬;黃權(quán);糜竺;鄧芝;簡雍;孫乾     |



查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進(jìn)行分隔。

如果不指定分隔符，GROUP_CONCAT 函數(shù)默認(rèn)使用逗號進(jìn)行分隔。GROUP_CONCAT 函數(shù)忽略輸入?yún)?shù)中的空值。同時，該函數(shù)不支持分組內(nèi)的數(shù)據(jù)排序，按照隨意順序進(jìn)行字符串合并。
總結(jié)

本文介紹了 5 種主流數(shù)據(jù)庫中的字符串聚合函數(shù)，使用時需要注意不同數(shù)據(jù)庫中的參數(shù)差異。