MySQL中如何按parent_id分组查找list_index列的序列间隙？

问题背景 #

我查了不少关于查找序列间隙的SQL方案，但都适配不了我的场景——得按parent_id分组来检查序列完整性。

我的简化表结构如下：

id | parent_id | list_index
1  | 987       | 0
2  | 987       | 1
3  | 987       | 2

4  | 654       | 0
5  | 654       | 2

7  | 321       | 1
8  | 321       | 2
9  | 321       | 4
10 | 321       | 5

业务规则要求：每个parent_id对应的list_index必须是从0开始的连续整数序列，但现有数据存在间隙。我不需要知道具体缺了哪些索引值，只要找出存在至少一个间隙的parent_id就行。

比如示例数据里，parent_id=654缺了list_index=1，parent_id=321缺了list_index=0和3，这两个需要被查出；而parent_id=987的序列连续，不应被包含。

解决方案 #

可以通过分组统计每个parent_id的关键指标来快速判断：

• 分组内最小的list_index是否为0
• 分组内最大的list_index是否等于（该分组的记录总数-1）

如果序列是从0开始的连续整数，那么最大索引必然等于记录数减一，同时最小索引必须是0。只要这两个条件有一个不满足，就说明存在间隙。

对应的SQL语句如下：

SELECT parent_id
FROM your_table_name
GROUP BY parent_id
HAVING MIN(list_index) != 0 
   OR MAX(list_index) != COUNT(*) - 1;

逻辑说明 #

1. GROUP BY parent_id：按父ID分组，单独校验每个分组的序列
2. MIN(list_index) != 0：如果分组里最小的索引不是0，说明缺失了起始值0（比如示例中的321）
3. MAX(list_index) != COUNT(*) - 1：假设序列连续且从0开始，N条记录的最大索引应该是N-1。如果不等，说明中间存在间隙（比如示例中的654，总记录数2，最大索引2≠1；321总记录数4，最大索引5≠3）

内容的提问来源于stack exchange，提问作者E-Riz