如何用Full Join正确关联三表实现src__id+Period维度聚合？

现有三个CTE结果集，均包含src__id和Period字段：

CTE结果集1（记为A） #

CTE结果集2（记为B） #

CTE结果集3（记为C） #

期望合并后得到如下结果，按src__id+Period分组展示各金额，并计算A+B-C：

语句1：仅按src__id关联 #

SELECT 
    COALESCE(A.src__id, B.src__id, C.src__id) AS src__id,
    COALESCE(A.Period, B.Period, C.Period) AS Period,
    A.Amount_A AS A,
    B.Amount_B AS B,
    C.Amount_C AS C,
    COALESCE(A.Amount_A, 0) + COALESCE(B.Amount_B, 0) - COALESCE(C.Amount_C, 0) AS "A+B-C"
FROM A
FULL JOIN B ON A.src__id = B.src__id
FULL JOIN C ON A.src__id = C.src__id

错误原因：只通过src__id关联，未结合Period，导致id2的Q2数据在B和C中无法匹配到同一行，最终拆分成两条独立记录。

语句3：关联C时完全依赖B的字段 #

SELECT 
    COALESCE(A.src__id, B.src__id, C.src__id) AS src__id,
    COALESCE(A.Period, B.Period, C.Period) AS Period,
    A.Amount_A AS A,
    B.Amount_B AS B,
    C.Amount_C AS C,
    COALESCE(A.Amount_A, 0) + COALESCE(B.Amount_B, 0) - COALESCE(C.Amount_C, 0) AS "A+B-C"
FROM A
FULL JOIN B ON A.src__id = B.src__id AND A.Period = B.Period
FULL JOIN C ON B.src__id = C.src__id AND B.Period = C.Period

错误原因：当B中没有对应src__id+Period的记录时（比如存在只有A和C有数据的场景），C的记录会被遗漏——因为关联条件完全依赖B的字段，此时B的字段为NULL，无法匹配C的记录。

要确保所有src__id+Period的组合都被保留，且三个表的对应数据能匹配到同一行，最优方式是先获取所有唯一的src__id+Period组合，再分别左连接三个CTE：

WITH all_keys AS (
    SELECT src__id, Period FROM A
    UNION
    SELECT src__id, Period FROM B
    UNION
    SELECT src__id, Period FROM C
)
SELECT 
    ak.src__id,
    ak.Period,
    A.Amount_A AS A,
    B.Amount_B AS B,
    C.Amount_C AS C,
    COALESCE(A.Amount_A, 0) + COALESCE(B.Amount_B, 0) - COALESCE(C.Amount_C, 0) AS "A+B-C"
FROM all_keys ak
LEFT JOIN A ON ak.src__id = A.src__id AND ak.Period = A.Period
LEFT JOIN B ON ak.src__id = B.src__id AND ak.Period = B.Period
LEFT JOIN C ON ak.src__id = C.src__id AND ak.Period = C.Period;

原理说明 #

1. all_keys通过UNION获取三个表中所有不重复的src__id+Period组合，确保不会遗漏任何边缘场景（比如只有A有数据、只有C有数据、A和C有数据但B没有等）。
2. 以all_keys为基础左连接三个CTE，保证每个src__id+Period组合都能对应到三个表的对应数据，没有匹配的则显示NULL。

你给出的这条语句：

SELECT 
    COALESCE(A.src__id, B.src__id, C.src__id) AS src__id,
    COALESCE(A.Period, B.Period, C.Period) AS Period,
    A.Amount_A AS A,
    B.Amount_B AS B,
    C.Amount_C AS C,
    COALESCE(A.Amount_A, 0) + COALESCE(B.Amount_B, 0) - COALESCE(C.Amount_C, 0) AS "A+B-C"
FROM A
FULL JOIN B ON A.src__id = B.src__id AND A.Period = B.Period
FULL JOIN C ON B.src__id = C.src__id

当前场景正确但存在隐患的原因：

• 当前场景中，C的id2+Q2刚好和B的id2+Q2匹配，所以能关联到同一行。
• 但如果存在C有id3+Q3但B中没有该组合的情况，这条语句会把C的这条记录单独拆成一行；如果A有id3+Q3但B没有，C的id3+Q3也无法和A的记录合并，最终出现重复的id3+Q3行，不符合需求。

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