如何在MySQL中查找重复项和间隙

Question

你好，我有一个类似下面的表格：

-----------------------------------------------------------
|  id  |  group_id | source_id | target_id | sortsequence |
-----------------------------------------------------------
|  2   |    1      |    2      |   4       |     1        |   
-----------------------------------------------------------
|  4   |    1      |    20     |   2       |     1        |   
-----------------------------------------------------------
|  5   |    1      |    2      |   14      |     1        |   
-----------------------------------------------------------
|  7   |    1      |    2      |   7       |     3        |   
-----------------------------------------------------------
|  20  |    2      |    20     |   4       |     3        |   
-----------------------------------------------------------
|  21  |    2      |    20     |   4       |     1        |   
-----------------------------------------------------------

场景

需要处理两种情况：

1. Sortsequence 列的值应在一个 source_id 和 group_id 中是唯一的。例如，如果所有记录都具有 group_id = 1 AND source_id = 2，则 sortsequence 应该是唯一的。在上面的示例中，具有 id=5 和 6 的记录，其 group_id = 1 和 source_id = 2，它们具有相同的 sortsequence 值，即 1，这是错误的记录。我需要找出这些记录。
2. 如果group_id 和 source_id 相同，则sortsequence列的值应该是连续的。不应存在间隔。例如，在上表中，具有id = 20, 21的记录具有相同的 group_id 和 source_id，并且它们的 sortsequence 值分别为 3 和 1。即使这是唯一的，但是 sortsequence 值中存在一个间隔。我也需要找出这些记录。

目前的工作进展

我已经编写了一个查询。

SELECT source_id,`group_id`,GROUP_CONCAT(id) AS children 
FROM
    table 
GROUP BY source_id,
  sortsequence,
  `group_id` 
 HAVING COUNT(*) > 1 

这个查询只涉及到场景1。如何处理场景2？有没有办法在同一个查询中处理它，还是我需要编写其他内容来处理第二个场景。

顺便说一下，查询将处理表中数百万条记录，因此性能必须非常好。

- Awais Qarni

---

排序序列是否总是以1开始的？如果是，您可以获取（source_id，group_id）的sortsequence计数和总和，并应用求n个数字的总和的公式。 - Chetter Hummin

@ChetterHummin 是的，它必须从1开始。 - Awais Qarni

然后我会将这些值求和，看看它是否等于count * (count + 1) / 2。这可能需要一个内部查询。Oracle可能有一些分析函数可以为您完成此操作。但我不知道在mysql中的等效方法。 - Chetter Hummin

可能与https://dev59.com/MGsz5IYBdhLWcg3wR1kc有关。 - didierc

@Chetter Hummin 只按 group_id 和 source_id 进行分组，然后检查 COUNT(DISTINCT sortsequence) <> COUNT(sortsequence) OR COUNT(sortsequence) <> MAX(sortsequence) 的组，可以同时检测到这两个问题。 - Terje D.

@TerjeD。是的，肯定有一个更简单的解决方案。 - Chetter Hummin

Answer 1

我从 Tere J 的评论中得到了答案。以下查询涵盖了上述两个标准。

 SELECT 
     source_id, `group_id`, GROUP_CONCAT(id) AS faultyIDS    
 FROM
     table
 GROUP BY
     source_id,group_id 
 HAVING
     COUNT(DISTINCT sortsequence) <> COUNT(sortsequence) OR COUNT(sortsequence) <> MAX(sortsequence) OR MIN(sortsequence) <> 1

也许它能帮助其他人。

- Awais Qarni

Answer 2

尝试使用这个查询语句，它可以解决你在问题中提到的两种情况。

SELECT 
   a.* 
FROM 
   tbl a
INNER JOIN 
   (select 
       @rn:=IF(@prevG = group_id AND @prevS = source_id, @rn + 1, 1) As rId,
       @prevG:=group_id AS group_id, 
       @prevS:=source_id AS source_id, 
       id, 
       sortsequence
    FROM 
       tbl 
    join 
       (select @rn:=0, @prevS:=0, @prevG:=0)b
    order by group_id, source_id, id) b
ON a.id = b.id AND a.SORTSEQUENCE <> b.RID;

代码片段

- Meherzad