SQL 列表串联

2024-04-14 19:18:03 478

示例

部分归功于此SO答案。

列表串联通过将值组合成每个组的单个字符串来聚合列或表达式。可以指定用于分隔每个值的字符串（如果省略，则为空白或逗号），并且可以指定结果中值的顺序。尽管它不是SQL标准的一部分，但是每个主要的关系数据库供应商都以自己的方式支持它。

的MySQL

SELECT ColumnA
     , GROUP_CONCAT(ColumnB ORDER BY ColumnB SEPARATOR ',') AS ColumnBs
  FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

Oracle和DB2

SELECT ColumnA
     , LISTAGG(ColumnB, ',') WITHIN GROUP (ORDER BY ColumnB) AS ColumnBs
  FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

PostgreSQL的

SELECT ColumnA
     , STRING_AGG(ColumnB, ',' ORDER BY ColumnB) AS ColumnBs
  FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

SQL服务器

SQLServer2016及更早版本

（包括CTE以鼓励DRY原则）

  WITH CTE_TableName AS (
       SELECT ColumnA, ColumnB
         FROM TableName)
SELECT t0.ColumnA
     , STUFF((
       SELECT ',' + t1.ColumnB
         FROM CTE_TableName t1
        WHERE t1.ColumnA = t0.ColumnA
        ORDER BY t1.ColumnB
          FOR XML PATH('')), 1, 1, '') AS ColumnBs
  FROM CTE_TableName t0
 GROUP BY t0.ColumnA
 ORDER BY ColumnA;

SQLServer2017和SQLAzure

SELECT ColumnA
     , STRING_AGG(ColumnB, ',') WITHIN GROUP (ORDER BY ColumnB) AS ColumnBs
  FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

SQLite的

无需订购：

SELECT ColumnA
     , GROUP_CONCAT(ColumnB, ',') AS ColumnBs
  FROM TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

订购需要子查询或CTE：

  WITH CTE_TableName AS (
       SELECT ColumnA, ColumnB
         FROM TableName
        ORDER BY ColumnA, ColumnB)
SELECT ColumnA
     , GROUP_CONCAT(ColumnB, ',') AS ColumnBs
  FROM CTE_TableName
 GROUP BY ColumnA
 ORDER BY ColumnA;

SQL 列表串联

示例

的MySQL

Oracle和DB2

PostgreSQL的

SQL服务器

SQLServer2016及更早版本

SQLServer2017和SQLAzure

SQLite的

热门推荐

随机推荐