多左连接同一列不同值时避免行重复的SQL实现求助

问题背景 #

• DECLARATIONS表：存储进口报关主数据，核心字段包括declaration_id(主键)、date、weight、price等
• DOCUMENTATION表：存储报关对应的单据数据，核心字段包括declaration_id(外键关联主表)、doc_type、doc_number等
• 需求：同一declaration_id下最多有8份doc_type='U44'的单据，需将每份报关单的所有相关单据号合并到一行，输出字段为declaration_id、date、doc_number1至doc_number8

当前方法的问题 #

你尝试通过多次左连接DOCUMENTATION表来获取多列单据号，但这种方式会因为多表连接时的笛卡尔积产生重复行——比如3份单据会生成3×2×1=6行冗余数据，无法满足“一行对应一份报关单”的要求。

正确解决方案：先编号再转列 #

通用版（兼容MySQL、PostgreSQL、SQL Server等多数数据库） #

使用ROW_NUMBER()为每个报关单下的U44单据编号，再通过条件聚合将编号后的单据号转成对应列：

SELECT
    d.declaration_id,
    d.date,
    MAX(CASE WHEN rn = 1 THEN doc_number END) AS doc_number1,
    MAX(CASE WHEN rn = 2 THEN doc_number END) AS doc_number2,
    MAX(CASE WHEN rn = 3 THEN doc_number END) AS doc_number3,
    MAX(CASE WHEN rn = 4 THEN doc_number END) AS doc_number4,
    MAX(CASE WHEN rn = 5 THEN doc_number END) AS doc_number5,
    MAX(CASE WHEN rn = 6 THEN doc_number END) AS doc_number6,
    MAX(CASE WHEN rn = 7 THEN doc_number END) AS doc_number7,
    MAX(CASE WHEN rn = 8 THEN doc_number END) AS doc_number8
FROM
    DECLARATIONS d
LEFT JOIN (
    -- 子查询：给每个报关单下的U44单据按编号排序
    SELECT
        declaration_id,
        doc_number,
        ROW_NUMBER() OVER (PARTITION BY declaration_id ORDER BY doc_number) AS rn
    FROM
        DOCUMENTATION
    WHERE
        doc_type = 'U44'
) t ON d.declaration_id = t.declaration_id
GROUP BY
    d.declaration_id, d.date;

SQL Server专属版（使用PIVOT函数） #

如果使用SQL Server，也可以用PIVOT语法实现：

SELECT
    declaration_id,
    date,
    [1] AS doc_number1,
    [2] AS doc_number2,
    [3] AS doc_number3,
    [4] AS doc_number4,
    [5] AS doc_number5,
    [6] AS doc_number6,
    [7] AS doc_number7,
    [8] AS doc_number8
FROM (
    SELECT
        d.declaration_id,
        d.date,
        t.doc_number,
        t.rn
    FROM
        DECLARATIONS d
    LEFT JOIN (
        SELECT
            declaration_id,
            doc_number,
            ROW_NUMBER() OVER (PARTITION BY declaration_id ORDER BY doc_number) AS rn
        FROM
            DOCUMENTATION
        WHERE
            doc_type = 'U44'
    ) t ON d.declaration_id = t.declaration_id
) src
PIVOT (
    MAX(doc_number)
    FOR rn IN ([1], [2], [3], [4], [5], [6], [7], [8])
) pvt;

方案说明 #

1. 子查询中ROW_NUMBER() OVER (PARTITION BY declaration_id ORDER BY doc_number)：按报关单分组，给每组内的U44单据按doc_number排序并生成唯一编号（1到8）
2. 外层通过MAX(CASE...)或PIVOT，将不同编号的单据号映射到对应的doc_number1至doc_number8列
3. 最后按报关单主表的declaration_id和date分组，确保每份报关单只输出一行数据，彻底避免重复

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