模拟Access中的完整外部联接 [英] Simulating a FULL OUTER JOIN in Access

问题描述

我需要连接两个表并获得如下输出

I need to join two tables and get the output as follows

T1

S    B   C   P
---  --  --  --
ABC  B1  C1  10
ABC  B2  C2  11
ABC  B3  C3  12

T2

S    B   C   P
---  --  --  --
ABC  B1  C1  12
ABC  B2  C2  13
ABC  B5  C5  14

我需要以下输出

S    B   C   P1  P2
---  --  --  --  --
ABC  B1  C1  10  12
ABC  B2  C2  11  13
ABC  B3  C3  12  0
ABC  B5  C5  0   14

请注意:我正在使用Microsoft Access，并且Access SQL不直接支持FULL OUTER JOIN查询.

Please note: I am using Microsoft Access and Access SQL does not directly support FULL OUTER JOIN queries.

推荐答案

您可以模拟FULL OUTER JOIN:

You can simulate FULL OUTER JOIN:

SELECT 
    T1.S, T1.B, T1.C, T1.P AS P1, IIF(ISNULL(T2.P), 0, T2.P) AS P2 
FROM
    T1 LEFT JOIN
    T2 ON (T1.S = T2.S AND T1.B = T2.B AND T1.C = T2.C)
UNION ALL
SELECT 
    T2.S, T2.B, T2.C, IIF(ISNULL(T1.P), 0, T1.P) AS P1, T2.P AS P2 
FROM
    T2 LEFT JOIN
    T1 ON (T1.S = T2.S AND T1.B = T2.B AND T1.C = T2.C)
WHERE T1.S IS NULL

这篇关于模拟Access中的完整外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！