你好,我有一个表,看起来像
-----------------------------------------------------------
| 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 |
-----------------------------------------------------------
有两种情况需要处理。
-
Sortsequence
列值在source_id
和group_id
中必须是唯一的。例如,如果所有具有group_id = 1 AND source_id = 2
的记录都应该具有唯一的排序顺序。在上面的例子中,记录具有id= and 5 which are having group_id = 1 and source_id = 2 have same sortsequence which is 1
。这是错误的记录。我需要找到这些记录。 - 如果
group_id and source_id
相同。sortsequence columns value should be continous. There should be no gap
。例如在上面的表records having id = 20, 21 having same group_id and source_id and sortsequence value is 3 and 1
中。即使这是唯一的,但在排序序列值中存在差距。我也需要找到这些记录。
MY So Far Effort
我写了一个查询SELECT source_id,`group_id`,GROUP_CONCAT(id) AS children
FROM
table
GROUP BY source_id,
sortsequence,
`group_id`
HAVING COUNT(*) > 1
该查询只针对场景1。如何处理场景2?是否有任何方法可以在相同的查询中做到这一点,或者我必须编写其他处理第二种情况。
By the way query will be dealing with million of records in table so performance must be very good.
从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
也许可以帮助别人。
试试这个查询,它将解决您在问题中提到的两种情况。
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;
小提琴