sql - 如何查找键重复但所有列中不重复的行？

Question

我正在处理一个表格，它是一组其他表格的摘录。根据键 D1、D2 和 D3，提取表的所有行都应该是唯一的。他们不是。似乎较早的开发人员试图通过使用 SELECT DISTINCT 来解决此问题。跨从该表查询的所有列。这将起作用，但前提是在 (D1, D2, D3) 上重复的每一行也是非键列中的重复项(忽略添加到提取表的 IDENTITY 列)。

换句话说，给定的行如下:

D1  D2  D3  C4  C5  C6
=== === === === === ===
A   B   C   X1  X2  X3
A   B   C   X1  X2  X3

然后

SELECT DISTINCT D1, D2, D3, C4, C5, C6
FROM BAD_TABLE

将“工作”，因为在(D1，D2，D3)上复制的行之间没有区别。但是如果表中包含

D1  D2  D3  C4  C5  C6
=== === === === === ===
A   B   C   X1  X2  X3
A   B   C   X1  X2  X4

然后 SELECT DISTINCT 将为键 (A,B,C) 返回两行。此外，我们必须决定 X3 或 X4 中的哪一个是“正确”的值。

我知道如何在 (D1,D2,D3) 上找到重复项。我什至知道如何在所有列(IDENTITY 列除外)中查找重复项:

;
WITH DUPLICATES(D1,D2,D3) AS
(
    SELECT D1, D2, D3
    FROM SOURCE
    GROUP BY D1, D2, D3
    HAVING COUNT(*)>1
)
SELECT S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
FROM SOURCE S
INNER JOIN DUPLICATES D
    ON S.D1 = D.D1 AND S.D2 = D.D2 AND S.D3 = D.D3
ORDER BY S.D1, S.D2, S.D3, S.C4, S.C5, S.C6

问题是，我如何找到在 (D1,D2,D3) 上重复的上述结果集的子集，但是 不是 重复(D1，D2，D3，C4，C5，C6)？

Answer 1

你可以通过加入表格本身来做到这一点，说 D 都是相等的，而 C 中至少有一个不相等。

CREATE TABLE #Source (
    D1 VARCHAR(2),
    D2 VARCHAR(2),
    D3 VARCHAR(2),
    C4 VARCHAR(2),
    C5 VARCHAR(2),
    C6 VARCHAR(2) );

INSERT INTO #Source VALUES ('A', 'B', 'C', 'X1', 'X2', 'X3');
INSERT INTO #Source VALUES ('A', 'B', 'C', 'X1', 'X2', 'X4');
INSERT INTO #Source VALUES ('A', 'B', 'D', 'X1', 'X2', 'X3');
INSERT INTO #Source VALUES ('A', 'B', 'D', 'X1', 'X2', 'X3');

SELECT S1.D1, S1.D2, S1.D3, S1.C4 C4_1, S2.C4 C4_2, S1.C5 C5_1, S2.C5 C5_2, S1.C6 C6_1, S2.C6 C6_2
FROM
    #Source S1
            INNER JOIN
    #Source S2
            ON
        (       S1.D1 = S2.D1 
            AND S1.D2 = S2.D2
            AND S1.D3 = S2.D3
            AND (   S1.C4 <> S2.C4
                 OR S1.C5 <> S2.C5
                 OR S1.C6 <> S2.C6
                 )
        );

DROP TABLE #Source;

给出以下结果:

D1   D2   D3   C4_1 C4_2 C5_1 C5_2 C6_1 C6_2
---- ---- ---- ---- ---- ---- ---- ---- ----
A    B    C    X1   X1   X2   X2   X4   X3
A    B    C    X1   X1   X2   X2   X3   X4

另请注意，这与 MS SQL 2000 兼容，因为您稍后在 How to Convert a SQL Query using Common Table Expressions to One Without (for SQL Server 2000) 中指出这是必需的。 .