mysql - 另一种间隙检测

Question

看了很多例子，但我没有找到适合我具体情况的好答案。

基本上，我有一个表，其中包含唯一 ID 和每个 ID 的序列。我想检测每个 ID 序列中的间隙。我设法使用查询来做到这一点，但查询在 where 子句中采用了确切的 ID，并且考虑到我的表中的行数，这是不需要的。

CREATE TABLE `t` (
  `target_id` varchar(100) NOT NULL,
  `version` int(11) NOT NULL,
  PRIMARY KEY (`target_id`,`version`)
);

insert into t values
('abc',1),
('abc',2),
('abc',3),
('abc',4),
('abc',5),
('abc',6),
('abc',7),
('abc',8),
('xyz',1),
('xyz',2),
('xyz',3),
('xyz',5),
('xyz',6);

http://sqlfiddle.com/#!9/8c280a/7

我试过类似的东西

select distinct target_id as target, gap_ends_at,gap_starts_at  from 
category_event e inner join 
(
SELECT target_id as x, (t1.version + 1) as gap_starts_at, 
   (SELECT MIN(t3.version) -1 FROM category_event t3 WHERE t3.version > 
t1.version and target_id=e.target) as gap_ends_at
FROM category_event t1
WHERE NOT EXISTS (SELECT t2.version FROM category_event t2 WHERE t2.version = t1.version + 1 and target_id=e.target) and target_id=e.target
HAVING gap_ends_at IS NOT NULL
) as x;

但是失败了。

我希望得到这样的结果集id, gap_starts_at, gap_ends_at

Answer 1

因为这是 MySQL，you can solve it just using vars :

MySQL 5.6 架构设置:

CREATE TABLE `t` (
  `target_id` varchar(100) NOT NULL,
  `version` int(11) NOT NULL,
  PRIMARY KEY (`target_id`,`version`)
);

insert into t values
('abc',1),
('abc',2),
('abc',3),
('abc',4),
('abc',5),
('abc',6),
('abc',7),
('abc',8),
('xyz',1),
('xyz',2),
('xyz',3),
('xyz',5),
('xyz',6)

查询 1:

select 
  gap 
from (
 Select 
  target_id,
  @v := case when @t <> target_id then 1 else @v+1 end,
  @t := case when @t <> target_id then target_id else @t end,
  case when @v <> version then CONCAT_WS(' ' ,
                                    'gap for ',
                                    target_id,
                                    ' start at', 
                                    CAST(@v-1 as CHAR(50)) , 
                                    'ends at ', 
                                    CAST(version as CHAR(50))
                                     ) 
                     else null end as gap,
  @v := version
 from 
  (select target_id, version
   from t
   order by target_id, version ) S,
  ( select @t:='', @v=0 ) I
) X 
where gap > ''

Results :

|                                 gap |
|-------------------------------------|
| gap for  xyz  start at 3 ends at  5 |