如何查找按键重复但在所有列中不重复的行？-解网

问：

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

换言之，给定的行如下所示：

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，C4，C5，C6）上重复，但在（D1，D2，D3，C4，C5，C6）上不是重复的？

T-SQL SQL-SERVER-2008

1赞 WhatIsOpenID 11/23/2010

你能给一个TL吗？DR 版本？

6赞 John Saunders 11/23/2010

@WhatIs：如果我知道“TL：DR”是什么意思，我可以：-）

3赞 John Saunders 9/11/2011

对这个问题投反对票是怎么回事？如果你不说问题是什么，我该如何改进这个问题？

2赞 Dan Nissenbaum 1/26/2013

StackOverflow 正在被一小部分高声誉用户所破坏，他们认为他们有权在没有仔细阅读的情况下投反对票。只是我的 2c。

1赞 Aaron Bertrand 2/2/2013

@JohnSaunders几件事。（1）人们在投反对票时不必发表评论，它与声誉无关，除了最低声誉可以投反对票（只需要125）。正如你从这场辩论中看到的那样，这不会改变（很多人都喜欢它）。（2）如果在你发布问题近一年后发生了反对票，也许问题在于你没有接受答案。下面的答案都无助于解决问题吗？

答：

2赞 Conrad Frix 11/23/2010 #1

您有什么理由不创建另一个表表达式来覆盖更多字段并连接到该表达式吗？

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

0赞 John Saunders 11/23/2010

你已经很接近了，但我想看看哪些是“关键重复项”，而不是“完全重复项”。很抱歉不知道这些的正确术语。

0赞 Conrad Frix 11/23/2010

@John 当然是对的。我在第二个 CTE 上更新了答案，其 Count（*） = 1

3赞 2 revsJohn Saunders #2

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

因此，如果要查找集合 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）。

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

0赞 RC_Cleland 11/23/2010

对上述代码稍作更正：最后一行“ORDER BY D1， D2， D3， D4， C5， C6”应为“ORDER BY D1， D2， D3， C4， C5， C6”

0赞 Andriy M 6/3/2011

我找不到明确指出，如果具有相同键值的任何一组行的某些非键列存在差异，那么它肯定只是一行与其他行不同（或者，例如，如果只有两行，则为两行）。真的是这样吗？我的意思是，会不会有这样的情况，即四行仅在最后一列的值上有所不同，两行包含，另外两行包含？我的问题是，这种不一致是否也应该被考虑在内？(A B C X1 Y1 Z1), (A B C X1 Y1 Z1), (A B C X1 Y1 Z2), (A B C X1 Y1 Z2)Z1Z2

2赞 orbfish 11/23/2010 #3

这将有性能限制，但更容易理解：

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

想把它归结为一个查询......

0赞 John Saunders 11/23/2010

谢谢。我会调查的。是的，我们有分析函数（COUNT、ROW_COUNT等）。

0赞 orbfish 11/24/2010

重要的是能够按不同的分组进行分区，这样你只需要进行一次传递 - 我认为有一种方法可以做到这一点，我只是不知道语法是否是标准的。

4赞 Samuel Neff 2/18/2011 #4

你可以通过连接表格本身来做到这一点，说 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 兼容，正如您稍后在如何使用公用表表达式将 SQL 查询转换为不带公用表表达式的 SQL 查询（对于 SQL Server 2000）中指出的那样。

0赞 John Saunders 2/18/2011

我午餐时间的任务是尝试一下。我仍然在处理这些相同的查询，尽管我被允许在修复它们时进行单元测试（实际上是 TDD）。

2赞 Cade Roux 2/2/2013 #5

我知道这是一个老问题，但我看到了关于这个问题的活动，而且我总是用这些技术在这里没有作为答案呈现，而且它真的很简单，所以我想我会提出来。

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 作为原始问题中的示例。

上一个：使用分支时，如何确定特定 TFS 生成中修复的工作项？

下一个：如何从层次结构的中间删除分支？

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

How to Find Rows which are Duplicates by a Key but Not Duplicates in All Columns?

评论

评论

评论

评论

评论