代码之家 › 专栏 › 技术社区 › John Saunders Andrey Morozov

如何查找按键重复但不在所有列中重复的行?

sql-server-2008 tsql sql

John Saunders Andrey Morozov · 技术社区 · 14 年前

我正在处理一张桌子,它是一组其他桌子的摘录。根据键D1、D2和D3,提取表的所有行都应该是唯一的。他们不是。似乎早期的开发人员试图通过使用 SELECT DISTINCT 从该表中查询的所有列。这将起作用,但前提是在(D1、D2、D3)上重复的每一行在非键列上也是重复的(忽略添加到提取表中的标识列)。

换句话说,给定行如下:

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)上找到副本。我甚至知道如何在所有列(标识列除外)中查找重复项:

;
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)?

5 回复 | 直到 13 年前

Conrad Frix 14 年前

为什么不创建另一个表表达式来覆盖更多的字段并加入到该表表达式中?

WITH DUPLICATEKEY(D1,D2,D3) AS
(
    SELECT D1, D2, D3
    FROM SOURCE
    GROUP BY D1, D2, D3
    HAVING COUNT(*)>1
)
WITH NODUPES(D1,D2,D3,C4,C5,C6) AS
(
SELECT 
S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
FROM SOURCE S
GROUP BY
 S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
HAVING COUNT(*)=1
)

SELECT S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
FROM SOURCE S
INNER JOIN DUPLICATEKEY D
    ON S.D1 = D.D1 AND S.D2 = D.D2 AND S.D3 = D.D3

INNER JOIN NODUPES D2
    ON S.D1 = D2.D1 AND S.D2 = D2.D2 AND S.D3 = D2.D3

ORDER BY S.D1, S.D2, S.D3, S.C4, S.C5, S.C6

Community arnoo 7 年前

你可以把表连接起来,说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) .

John Saunders 14 年前

我还没有机会尝试康拉德的答案,但我自己想出了一个。这是一个相当“糟糕”的时刻。

因此,如果要查找集合A中除集合B中的行以外的所有行,可以使用except运算符:

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

这似乎向我展示了1500行,它们是跨(D1,D2,D3)复制的,但它们只是跨(D1,D2,D3,C4,C5,C6)子集复制的。事实上,它们似乎是重复的(D1、D2、D3、C4、C5)。

如何确认这一点将是另一个问题的主题。

orbfish 14 年前

这会有性能限制,但更容易理解:

SELECT D1, D2, D3
FROM TEST_KEY TK
WHERE (D1, D2, D3) IN
        (SELECT D1, D2, D3 FROM TEST_KEY TK2
         GROUP BY D1, D2, D3
         HAVING COUNT(*) > 1)
  AND (D1, D2, D3) IN
        (SELECT D1, D2, D3 FROM TEST_KEY TK2
         GROUP BY D1, D2, D3, C4, C5, C6
         HAVING COUNT(*) < 2)

无法在SQL Server上测试,希望语法正确。

同样,不确定您是否在SQL Server中有分析功能,但这一功能在Oracle中工作,可能更快:

WITH BAD_DUP AS (
SELECT TK.*,
       COUNT(1) OVER (PARTITION BY D1, D2, D3, C4, C5, C6 ORDER BY D1) FULL_DUP,
       COUNT(1) OVER (PARTITION BY D1, D2, D3 ORDER BY D1) KEY_DUP
FROM TEST_KEY TK)
SELECT * FROM BAD_DUP
WHERE FULL_DUP < KEY_DUP

想把它归结为一个单一的查询。。。。

Cade Roux 11 年前

我知道这是一个老问题,但是我看到了这个问题的活动,我经常使用的技巧并没有作为答案呈现在这里,它非常简单,所以我想我会呈现它。

SELECT D1, D2, D3, MIN(C4), MAX(C4), MIN(C5), MAX(C5), MIN(C6), MAX(C6)
FROM BAD_TABLE
GROUP BY D1, D2, D3
HAVING MIN(C4) <> MAX(C4)
    OR MIN(C5) <> MAX(C5)
    OR MIN(C6) <> MAX(C6)

这将在键上显示重复项的所有键,但在非键上显示差异,并显示差异重复项的范围。

要查看其中的所有行,您需要将其连接回BAD_TABLE,作为原始问题中的示例。