個人中心
退出
SQL 聚合函數(shù)之字符串分組合并
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及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ù)
總結
SQL 聚合函數(shù)（Aggregate Function）可以對一組數(shù)據(jù)進行匯總并返回單個結果。我們常見的聚合函數(shù)有 COUNT、AVG、SUM 等。今天給大家介紹一下如何通過 SQL 函數(shù)對字符串進行聚合，也就是將多行字符串合并成單個字符串。本文涉及的數(shù)據(jù)庫包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。
Oracle 中的 LISTAGG 函數(shù)
Oracle 實現(xiàn)了 SQL:2016 標準中的 LISTAGG 函數(shù)，例如（示例數(shù)據(jù)）以下查詢返回了每個部門中的員工：
SELECT dept_id, LISTAGG(emp_name, ';')
FROM employee
GROUP BY dept_id;
DEPT_ID|LISTAGG(EMP_NAME,';') |
-------|-----------------------------------------|
1|劉備;關羽;張飛 |
2|諸葛亮;黃忠;魏延 |
3|孫尚香;孫丫鬟 |
4|趙云;廖化;關平;趙氏;關興;張苞;趙統(tǒng);周倉;馬岱|
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;鄧芝;簡雍;孫乾 |
查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進行分隔。
如果想要在合并之前將員工進行排序，可以使用 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|趙云;廖化;周倉;關平;關興;趙氏;趙統(tǒng);張苞;馬岱 |
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;孫乾;鄧芝;簡雍 |
對于每個部門中的員工，首先按照入職日期進行排序，入職日期相同再按照月薪從高到低進行排序，然后將員工姓名進行合并。
除此之外，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 表示排除每個分組中的重復數(shù)據(jù)，ALL（默認值）表示使用全部數(shù)據(jù)；默認的分隔符為 NULL；listagg_overflow_clause 用于處理合并結果超長的情況，ON OVERFLOW ERROR（默認值）表示超長時返回錯誤，ON OVERFLOW TRUNCATE 表示超長時截斷字符串；OVER 子句表示將該函數(shù)當作分析函數(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|劉備;關羽;張飛 |
2|諸葛亮;黃忠;魏延 |
3|孫尚香;孫丫鬟 |
4|趙云;廖化;關平;趙氏;關興;張苞;趙統(tǒng);周倉;馬岱 |
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;鄧芝;簡雍;孫乾 |
查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進行分隔。
如果想要在合并之前將員工進行排序，可以使用 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|趙云;廖化;周倉;關平;關興;趙氏;趙統(tǒng);張苞;馬岱 |
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;孫乾;鄧芝;簡雍 |
對于每個部門中的員工，首先按照入職日期進行排序，入職日期相同再按照月薪從高到低進行排序，然后將員工姓名進行合并。
除此之外，GROUP_CONCAT 函數(shù)還支持一些其他選項，完整的語法如下：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
其中，DISTINCT 表示排除每個分組中的重復數(shù)據(jù)，默認表示使用全部數(shù)據(jù)；ORDER BY 表示聚合之前先排序；SEPARATOR 子句表用于指定分隔符，默認為逗號。
GROUP_CONCAT 函數(shù)忽略輸入?yún)?shù)中的空值。如果 GROUP_CONCAT 函數(shù)返回的結果超過系統(tǒng)變量 group_concat_max_len 設置的長度，將會截斷合并后的字符串。更多信息可以參考 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|劉備;關羽;張飛 |
2|諸葛亮;黃忠;魏延 |
3|孫尚香;孫丫鬟 |
4|趙云;廖化;關平;趙氏;關興;張苞;趙統(tǒng);周倉;馬岱|
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;鄧芝;簡雍;孫乾 |
查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進行分隔。
如果想要在合并之前將員工進行排序，可以使用 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|趙云;廖化;周倉;關平;關興;趙氏;趙統(tǒng);張苞;馬岱|
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;孫乾;鄧芝;簡雍 |
對于每個部門中的員工，首先按照入職日期進行排序，入職日期相同再按照月薪從高到低進行排序，然后將員工姓名進行合并。
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|趙云;廖化;關平;趙氏;關興;張苞;趙統(tǒng);周倉;馬岱|
2|諸葛亮;黃忠;魏延 |
3|孫尚香;孫丫鬟 |
1|劉備;關羽;張飛 |
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;鄧芝;簡雍;孫乾 |
查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進行分隔。
如果想要在合并之前將員工進行排序，可以使用 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|趙云;廖化;周倉;關平;關興;趙氏;趙統(tǒng);張苞;馬岱|
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;孫乾;鄧芝;簡雍 |
對于每個部門中的員工，首先按照入職日期進行排序，入職日期相同再按照月薪從高到低進行排序，然后將員工姓名進行合并。
STRING_AGG 函數(shù)的完整語法如下：
STRING_AGG ( [ALL|DISTINCT] expression, separator [ORDER BY …]) [ FILTER ( WHERE filter_clause ) ]
1
其中，DISTINCT 表示排除每個分組中的重復數(shù)據(jù)，ALL（默認值）表示使用全部數(shù)據(jù)；FILTER 子句用于指定一個過濾條件，只有滿足條件的數(shù)據(jù)才會進行聚合。STRING_AGG 函數(shù)忽略輸入?yún)?shù)中的空值。
關于聚合函數(shù)的詳細介紹，可以參考 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|劉備;關羽;張飛 |
2|諸葛亮;黃忠;魏延 |
3|孫尚香;孫丫鬟 |
4|趙云;廖化;關平;趙氏;關興;張苞;趙統(tǒng);周倉;馬岱 |
5|法正;龐統(tǒng);蔣琬;黃權;糜竺;鄧芝;簡雍;孫乾 |
查詢將每個部門中的多個員工姓名合并成了一行數(shù)據(jù)，多個姓名之間使用分號進行分隔。
如果不指定分隔符，GROUP_CONCAT 函數(shù)默認使用逗號進行分隔。GROUP_CONCAT 函數(shù)忽略輸入?yún)?shù)中的空值。同時，該函數(shù)不支持分組內(nèi)的數(shù)據(jù)排序，按照隨意順序進行字符串合并。
總結
本文介紹了 5 種主流數(shù)據(jù)庫中的字符串聚合函數(shù)，使用時需要注意不同數(shù)據(jù)庫中的參數(shù)差異。
分類導航