DB2 SQL查询需求：将子表多行数据合并至父表单行展示

需求说明 #

编写DB2 SQL查询，将子表中对应父表的多条记录合并到父表的单行中展示。父表与子表通过ROW_ID字段关联，父表每个账户对应唯一行，子表每个父表行对应多条记录。

测试数据 #

父表结构及数据

ROW_ID      DATE        ACCT
1234563   2024-01-31   333555
1234564   2024-01-31   333666
1234565   2024-01-31   333777

子表结构及数据

ROW_ID     CRD_DB_YN    AMT   CODE
1234563         D       100     A
1234563         D       200     C
1234563         C       300     D
1234564         D       100     X
1234565         D       250     A
1234565         C       300     C

解决方案 #

使用行号生成+条件聚合实现转置，完整SQL如下：

WITH child_with_seq AS (
    -- 给每个父表ROW_ID对应的子表记录生成序号
    SELECT 
        ROW_ID,
        CRD_DB_YN,
        AMT,
        CODE,
        ROW_NUMBER() OVER (PARTITION BY ROW_ID ORDER BY CODE) AS seq_num
    FROM child_table -- 替换为实际子表名称
)
SELECT 
    p.ROW_ID,
    p.ACCT,
    -- 第一组子表字段
    MAX(CASE WHEN seq_num = 1 THEN c.CRD_DB_YN END) AS CRD_DB_YN,
    MAX(CASE WHEN seq_num = 1 THEN c.AMT END) AS AMT,
    MAX(CASE WHEN seq_num = 1 THEN c.CODE END) AS CODE,
    -- 第二组子表字段
    MAX(CASE WHEN seq_num = 2 THEN c.CRD_DB_YN END) AS CRD_DB_YN,
    MAX(CASE WHEN seq_num = 2 THEN c.AMT END) AS AMT,
    MAX(CASE WHEN seq_num = 2 THEN c.CODE END) AS CODE,
    -- 第三组子表字段（根据子表最大行数扩展）
    MAX(CASE WHEN seq_num = 3 THEN c.CRD_DB_YN END) AS CRD_DB_YN,
    MAX(CASE WHEN seq_num = 3 THEN c.AMT END) AS AMT,
    MAX(CASE WHEN seq_num = 3 THEN c.CODE END) AS CODE
FROM parent_table p -- 替换为实际父表名称
LEFT JOIN child_with_seq c ON p.ROW_ID = c.ROW_ID
GROUP BY p.ROW_ID, p.ACCT
ORDER BY p.ROW_ID;

关键逻辑说明 #

1. 行号生成：通过ROW_NUMBER() OVER (PARTITION BY ROW_ID ORDER BY CODE)给每个父表ID下的子表记录分配唯一序号，排序字段可根据实际业务调整（比如按AMT或插入时间）。
2. 条件聚合：利用MAX(CASE...)将不同序号的子表记录转成父表单行的多列。如果子表最多有N条记录，需对应添加N组CASE语句。
3. 左连接：确保父表所有记录都能展示，即使没有对应的子表数据。

输出结果 #

运行上述SQL后，将得到与期望一致的结果：

ROW_ID   ACCT     CRD_DB_YN   AMT    CODE  CRD_DB_YN   AMT    CODE  CRD_DB_YN   AMT    CODE
1234563   333555        D      100      A      D        200      C      C      300       D
1234564   333666        D      100      X 
1234565   333777        D      250      A      C        300      C

注意：部分SQL工具可能不允许重复列名，若遇到此问题，可给列名添加序号后缀（如CRD_DB_YN_1、CRD_DB_YN_2）来区分。

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