以下是我面临的情况:
我有两个表格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
我目前的查询类似于这样:
The query I have so far looks like similar to this:
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
WC_Part_Table
和WS_Part_Table
作为视图,并且不使用WHERE O.Operation_Key = 23149
条件,则可能会发现更好的结果。然后,您可以使用KenTaylor或Cybernate的视图(如果要手动检查并且结果太多,则可以添加任何此类条件)。 - ypercubeᵀᴹ