如何通过SQL将多行关联数据合并为多值字段并写入目标表

需求说明 #

需要编写SQL查询，将原查询中同一L4Process.ID对应的多行Expr1值合并为多值字段，写入指定目标表的Field1列。

原SQL查询 #

SELECT L4Process.ID, L3Process2.ID, Requirements.ID AS Expr1, Requirements.Title AS Expr2
FROM Requirements INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = 
L3Process2.L4ProcessStep.Value) ON Requirements.ID = L3Process2.Requirements.Value
ORDER BY L4Process.ID, L3Process2.ID, Requirements.ID;

原查询结果集 #

L4Process.ID    L3Process2.ID   Expr1   Expr2
2   1   5   可将寄售库存从一个寄售地点转移到另一个地点的能力
2   1   6   寄售报告（分析）- 报告寄售中持有的短期贷款库存
2   1   7   寄售中等待开票 - 收到客户PO编号前暂不完成寄售出库（ Goods Issue）
2   1   8   寄售报告（分析）- 按地区、客户地点统计寄售库存
2   1   10  可在寄售订单上输入并跟踪批次号的能力
2   1   12  可按销售人员（后备箱库存）报告寄售持有库存的能力
2   1   14  可将寄售持有库存销售给终端客户，并确定正确的本地公司地址增值税号的能力
3   1   5   可将寄售库存从一个寄售地点转移到另一个地点的能力
3   1   6   寄售报告（分析）- 报告寄售中持有的短期贷款库存
3   1   7   寄售中等待开票 - 收到客户PO编号前暂不完成寄售出库（ Goods Issue）
3   1   8   寄售报告（分析）- 按地区、客户地点统计寄售库存
3   1   10  可在寄售订单上输入并跟踪批次号的能力
3   1   12  可按销售人员（后备箱库存）报告寄售持有库存的能力
3   1   14  可将寄售持有库存销售给终端客户，并确定正确的本地公司地址增值税号的能力
4   1   5   可将寄售库存从一个寄售地点转移到另一个地点的能力
4   1   6   寄售报告（分析）- 报告寄售中持有的短期贷款库存
4   1   7   寄售中等待开票 - 收到客户PO编号前暂不完成寄售出库（ Goods Issue）
4   1   8   寄售报告（分析）- 按地区、客户地点统计寄售库存
4   1   10  可在寄售订单上输入并跟踪批次号的能力
4   1   12  可按销售人员（后备箱库存）报告寄售持有库存的能力
4   1   14  可将寄售持有库存销售给终端客户，并确定正确的本地公司地址增值税号的能力
5   2   4   可在寄售出库订单上包含额外物料类别（如免费或标准行项目(TAN)）的能力
5   2   9   可在需求计划中模拟贷款库存退货的能力
5   2   11  可在客户现场对寄售持有库存进行实物盘点的能力
5   2   12  可按销售人员（后备箱库存）报告寄售持有库存的能力
5   2   13  可报告客户现场寄售持有库存的能力
5   2   15  可在客户寄售地点之间转移库存的能力
5   2   16  可直接将寄售持有库存销售给客户的能力

目标表结构 #

ID  L4Process   Field1
2   S01.02.01.01-客户管理  (W)   
3   S01.02.01.01-客户管理  (W)   
4   S01.02.01.02-搜索客户 (W)    
5   S01.02.01.04-显示客户列表 (F)  
6   S01.02.01.04-显示客户列表 (F)  
7   S01.02.01.05-维护业务伙伴 (F)  

解决方案 #

1. 聚合生成多值字段 #

根据使用的数据库类型，选择对应的字符串聚合函数合并Expr1值：

MySQL/MariaDB

使用GROUP_CONCAT函数：

SELECT 
    L4Process.ID,
    GROUP_CONCAT(Requirements.ID SEPARATOR ', ') AS merged_expr1
FROM Requirements 
INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = L3Process2.L4ProcessStep.Value) 
    ON Requirements.ID = L3Process2.Requirements.Value
GROUP BY L4Process.ID
ORDER BY L4Process.ID;

SQL Server（2017及以上版本）

使用STRING_AGG函数：

SELECT 
    L4Process.ID,
    STRING_AGG(Requirements.ID, ', ') AS merged_expr1
FROM Requirements 
INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = L3Process2.L4ProcessStep.Value) 
    ON Requirements.ID = L3Process2.Requirements.Value
GROUP BY L4Process.ID
ORDER BY L4Process.ID;

Oracle

使用LISTAGG函数：

SELECT 
    L4Process.ID,
    LISTAGG(Requirements.ID, ', ') WITHIN GROUP (ORDER BY Requirements.ID) AS merged_expr1
FROM Requirements 
INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = L3Process2.L4ProcessStep.Value) 
    ON Requirements.ID = L3Process2.Requirements.Value
GROUP BY L4Process.ID
ORDER BY L4Process.ID;

2. 将聚合结果写入目标表 #

假设目标表名为TargetTable，通过关联更新将合并值写入Field1列：

MySQL/MariaDB

UPDATE TargetTable t
JOIN (
    SELECT 
        L4Process.ID,
        GROUP_CONCAT(Requirements.ID SEPARATOR ', ') AS merged_expr1
    FROM Requirements 
    INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = L3Process2.L4ProcessStep.Value) 
        ON Requirements.ID = L3Process2.Requirements.Value
    GROUP BY L4Process.ID
) agg ON t.ID = agg.ID
SET t.Field1 = agg.merged_expr1;

SQL Server

UPDATE t
SET t.Field1 = agg.merged_expr1
FROM TargetTable t
JOIN (
    SELECT 
        L4Process.ID,
        STRING_AGG(Requirements.ID, ', ') AS merged_expr1
    FROM Requirements 
    INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = L3Process2.L4ProcessStep.Value) 
        ON Requirements.ID = L3Process2.Requirements.Value
    GROUP BY L4Process.ID
) agg ON t.ID = agg.ID;

Oracle

MERGE INTO TargetTable t
USING (
    SELECT 
        L4Process.ID,
        LISTAGG(Requirements.ID, ', ') WITHIN GROUP (ORDER BY Requirements.ID) AS merged_expr1
    FROM Requirements 
    INNER JOIN (L4Process INNER JOIN L3Process2 ON L4Process.ID = L3Process2.L4ProcessStep.Value) 
        ON Requirements.ID = L3Process2.Requirements.Value
    GROUP BY L4Process.ID
) agg ON t.ID = agg.ID
WHEN MATCHED THEN
    UPDATE SET t.Field1 = agg.merged_expr1;

备注 #

• 若需要使用其他分隔符（如分号、换行符），修改SEPARATOR后的内容即可。
• 目标表中与L4Process.ID不匹配的记录，Field1不会被更新，可根据需求调整逻辑。

内容的提问来源于stack exchange，提问作者The Hawk