SQL行数据拼接时如何将多行字符串合并为一行并用逗号分隔？

Question

在SQL中，如何将多行字符串合并为一行并用逗号分隔是一个常见需求。例如，从一张订单表中提取所有与某个客户相关的订单号，并将它们合并成一个逗号分隔的字符串。不同数据库系统提供了不同的解决方案：MySQL可以使用`GROUP_CONCAT()`函数，Oracle可以使用`LISTAGG()`函数，而SQL Server则可以通过`STRING_AGG()`（2017及以上版本）或借助`FOR XML PATH`实现。以MySQL为例，假设有一张表`orders`，包含字段`customer_id`和`order_id`，可通过以下语句实现：`SELECT GROUP_CONCAT(order_id SEPARATOR ',') AS order_list FROM orders WHERE customer_id = ? GROUP BY customer_id;`。如果需要对结果排序，还可以添加`ORDER BY`子句。这种技术广泛应用于数据汇总、报表生成等场景。

Answer 1

1. 问题概述

在SQL查询中，将多行字符串合并为一行并用逗号分隔是一个常见的需求。这种技术通常用于数据汇总、报表生成等场景。例如，从订单表中提取所有与某个客户相关的订单号，并将它们以逗号分隔的形式展示。

不同的数据库系统提供了不同的解决方案：MySQL使用`GROUP_CONCAT()`函数，Oracle使用`LISTAGG()`函数，而SQL Server则可以通过`STRING_AGG()`（适用于2017及以上版本）或借助`FOR XML PATH`实现。

示例场景

• 假设有一张表`orders`，包含字段`customer_id`和`order_id`。
• 目标是从该表中提取所有与某个客户相关的订单号，并将其合并成一个逗号分隔的字符串。

2. MySQL中的解决方案

在MySQL中，可以使用`GROUP_CONCAT()`函数来实现这一需求。以下是一个具体的SQL语句：

SELECT GROUP_CONCAT(order_id SEPARATOR ',') AS order_list 
    FROM orders 
    WHERE customer_id = ? 
    GROUP BY customer_id;

如果需要对结果进行排序，可以在`GROUP_CONCAT()`函数中添加`ORDER BY`子句：

SELECT GROUP_CONCAT(order_id ORDER BY order_id SEPARATOR ',') AS order_list 
    FROM orders 
    WHERE customer_id = ? 
    GROUP BY customer_id;

customer_id	order_list
1	101,102,103
2	201,202

3. Oracle中的解决方案

在Oracle数据库中，可以使用`LISTAGG()`函数来实现类似的功能。以下是一个示例SQL语句：

SELECT LISTAGG(order_id, ',') WITHIN GROUP (ORDER BY order_id) AS order_list 
    FROM orders 
    WHERE customer_id = ? 
    GROUP BY customer_id;

`LISTAGG()`函数支持通过`WITHIN GROUP`子句指定排序规则。

注意事项

如果合并后的字符串长度超过Oracle默认限制（4000字节），可能会导致错误。此时可以考虑使用`XMLAGG()`作为替代方案。

4. SQL Server中的解决方案

在SQL Server 2017及以上版本中，可以使用`STRING_AGG()`函数实现字符串合并。以下是一个示例SQL语句：

SELECT STRING_AGG(order_id, ',') WITHIN GROUP (ORDER BY order_id) AS order_list 
    FROM orders 
    WHERE customer_id = ? 
    GROUP BY customer_id;

对于SQL Server 2016及更早版本，可以通过`FOR XML PATH`实现类似功能：

SELECT STUFF((
        SELECT ',' + CAST(order_id AS VARCHAR(10)) 
        FROM orders 
        WHERE customer_id = ? 
        ORDER BY order_id 
        FOR XML PATH('')), 1, 1, '') AS order_list;

流程图

graph TD;
    A[开始] --> B{选择数据库类型};
    B -->|MySQL| C[使用GROUP_CONCAT];
    B -->|Oracle| D[使用LISTAGG];
    B -->|SQL Server| E[使用STRING_AGG 或 FOR XML PATH];
    C --> F[完成];
    D --> F;
    E --> F;
    

5. 应用场景分析

这种技术广泛应用于以下场景：

• 数据汇总：将多个相关记录合并为单一记录。
• 报表生成：将多个字段值合并为一个字段值，便于展示。
• ETL处理：在数据转换过程中，将多行数据合并为一行。

无论是在小型项目还是企业级应用中，这种技术都能显著提升数据处理效率。