tsql - t-sql NOT IN 多列

Question

我有一个 Microsoft SQL 数据库，我试图在其中插入一些数据。我在 4 列上有一个唯一键，我想将多个表中的数据插入到该表中，同时检查数据以确保它不会违反键的唯一性。如果我在单列上这样做，我会做一个 NOT IN，就像这样..

INSERT TABLE_A (FLD_1)
    SELECT FLD_1
        FROM TBL_B
        INNER JOIN TBL_C
            ON TBL_B.FLD_1 = TBL_C.FLD_1
    WHERE TBL_B.FLD_1 NOT IN
        (
        SELECT TBL_A.FLD_1 FROM TBL_A
        )

有什么想法吗？

Answer 1

请改用 NOT EXISTS，因为您必须处理多个列。

http://www.techonthenet.com/sql/exists.php

编辑:

未经测试，但大致是这样的:

SELECT FLD_1
FROM TBL_B
INNER JOIN TBL_C  ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS 
    (
    SELECT TBL_A.FLD_1 FROM TBL_A INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1
    )

对于多列检查，大致如下:

SELECT FLD_1, FLD_2, FLD_3, FLD_4)
FROM TBL_B
INNER JOIN TBL_C  ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS 
    (
    SELECT TBL_A.FLD_1, TBL_A.FLD_2, TBL_A.FLD_3, TBL_A.FLD_4 
    FROM TBL_A 
    INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1 AND 
                        TBL_B.FLD2 = TBL_A.FLD2 AND 
                        TBL_B.FLD3 = TBL_A.FLD3 AND 
                        TBL_B.FLD4 = TBL_A.FLD4 
    )