gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-01 23:01:19 28 4
gpt4 key购买 nike

这是我面临的情况:

我有两个表 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 都是我使用查询构建的临时表,但我的困境是我必须 预查询 我感兴趣的操作键上的#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/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com