sql - 基于多个字段的完整外部联接

这是我面临的情况:

我有两个表 A 和 B。如果记录在表 A 中而不在表 B 中，则需要将它们添加到表 B。如果记录在表 B 中而不在表 A 中，则需要将它们从表 B 中删除. 这里的诀窍是两个键的混合形成了独特的组合

Table A
Operation_Key    Part_Key
1                1
1                2
2                1
2                3

Table B
Operation_Key    Part_Key  Record_Key
1                1         1
2                1         2
2                3         3
2                4         4

我正在尝试获得正确类型的查询，以便返回的结果看起来像

Results
Operation_Key        Part_Key  Record_Key   Action
1                    2         NULL         Add
2                    4         4            Delete

到目前为止，我的查询与此类似:

CREATE TABLE #Action_Table
(
  Action VARCHAR(6),
  Action_Bit INT,
  Operation_Key INT,
  Record_Key INT,
  Part_Key INT
)
INSERT INTO #Action_Table
SELECT
  CASE
    WHEN WS.Operation_Key IS NULL THEN 'Delete'
    WHEN WS.Operation_Key IS NOT NULL THEN 'Add'
  END Action,
  CASE
    WHEN WS.Operation_Key IS NULL THEN '0'
    WHEN WS.Operation_Key IS NOT NULL THEN '1'
  END Action_Bit,
  CASE
    WHEN WS.Operation_Key IS NULL THEN WC.Operation_Key
    WHEN WS.Operation_Key IS NOT NULL THEN WS.Operation_Key
  END Operation_Key,
  CASE
    WHEN WS.Operation_Key IS NULL THEN WC.Record_Key
    WHEN WS.Operation_Key IS NOT NULL THEN NULL
  END Workcenter_Component_Key,
  CASE
    WHEN WS.Operation_Key IS NULL THEN WC.Part_Key
    WHEN WS.Operation_Key IS NOT NULL THEN WS.Part_Key
  END Part_Key
FROM #WS_Part_Table WS
FULL OUTER JOIN #WC_Part_Table WC
  ON WC.Part_Key = WS.Part_Key
 AND WC.Operation_Key = WS.Operation_Key
WHERE (WS.Part_Key IS NULL or WC.Part_Key IS NULL) AND (WS.Operation_Key IS NULL or WC.Operation_Key IS NULL)

#WS_Part_Table 和 #WC_Part_Table 都是我使用查询构建的临时表，但我的困境是我必须对我感兴趣的操作键进行 PRE-QUERY #WC_Part_Table 查询，否则我得到结果太多了。

这是我用来创建#WC_Part_Table 的查询

    CREATE TABLE #WC_Part_Table
    (
      Operation_Key INT,
      Record_Key INT,
      Part_Key INT
    )
    -- Workcenter Component Table
    INSERT INTO #WC_Part_Table
    SELECT
      O.Operation_Key,
      WC.Record_Key,
      WC.Part_Key
    FROM Workcenter_Component WC
    JOIN Operation O
      ON O.Default_Workcenter_Key = WC.Workcenter_Key

 /* There is some reason why this next line is needed */
    WHERE O.Operation_Key = 23149

最佳答案

Tyr 以获取您发布的结果:

SELECT COALESCE(a.Operation_Key, b.Operation_Key) Operation_Key,
             COALESCE(a.Part_Key, b.Part_Key) Part_Key,
             Record_Key,
             CASE
                WHEN Record_Key IS NULL THEN 'Add'
                ELSE 'Delete'
            END Action
  FROM TableA a FULL OUTER JOIN TableB b
      ON a.Operation_Key = b.Operation_Key
        AND a.Part_Key = b.Part_Key
 WHERE (a.Operation_Key IS NULL) OR (b.Operation_Key IS NULL)

测试脚本:

CREATE TABLE #TableA
(
    Operation_Key INT,
    Part_Key INT
)

INSERT INTO #TableA
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 2,3

CREATE TABLE #TableB
(
    Operation_Key INT,
    Part_Key INT,
    Record_Key INT
)

INSERT INTO #TableB
SELECT 1,1,1
UNION
SELECT  2,1,2
UNION
SELECT 2,3,3
UNION
SELECT 2,4,4


SELECT  COALESCE(a.Operation_Key, b.Operation_Key) Operation_Key,
                COALESCE(a.Part_Key, b.Part_Key) Part_Key,
                Record_Key,
                CASE
                    WHEN Record_Key IS NULL THEN 'Add'
                    ELSE 'Delete'
                END Action
    FROM    #TableA a FULL OUTER JOIN #TableB b
        ON  a.Operation_Key = b.Operation_Key
     AND    a.Part_Key = b.Part_Key
 WHERE (a.Operation_Key IS NULL) OR (b.Operation_Key IS NULL)

输出:

Operation_Key   Part_Key    Record_Key  Action
1   2   NULL    Add
2   4   4   Delete

关于sql - 基于多个字段的完整外部联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5517834/