mysql - SQL检测 "sequence break"

Question

我有一些数据，其中增量序列在某处被破坏，可能多次。例如。 (2,3,4,5,6,8,10)。

我想得到:

1. 第一个“坏”的地方(例子中6是最后一个好)
2. “坏”地方的数量(2 次，在 7 和 9)

使用SQL(最好是通用的，适用于oracle和mysql等sql平台)

使用序列或 auto_increment 是特定于平台的。

我试过像

这样的自连接结构

select curr.id+1 as first_fail from junk as prev
join junk as curr 
on (prev.id+1 = curr.id) 
order by curr.id desc limit 1;

( http://sqlfiddle.com/#!9/bae781/4/0 )但它看起来很难看，并且无法通过这种方式获得“破损”地方的数量。

Answer 1

所有开始的“中断”按 id 排序:

select j1.id + 1 as id
from junk j1
left join junk j2 on j2.id = j1.id + 1
where j2.id is null
    and j1.id <> (select max(id) from junk)
order by j1.id;

选择第一行以获得第一个“中断”。计算行数以获得“中断”数。

如果您需要所有缺失 ID 的数量:

-- get number of missing ids
select 
    -- num rows you should have
    (select max(id) from junk) - (select min(id) from junk)     + 1
    -- num rows you really have
    - count(*) as num_missings
from junk;

或更短:

select max(id) - min(id) + 1 - count(*) as num_missings from junk;