mysql - MySQL在具有多个ID的列中找到空白

我想在下表中找出差距：

create table sequence
(
   `Id` int,
   `Value` int not null,
   PRIMARY KEY (`Id`,`Value`)
);

insert into sequence
    ( `Id`, `Value` )
  values
    (10, 0 ),
    (10, 1 ),
    (10, 4 ),
    (10, 5 ),
    (10, 6 ),
    (10, 7 ),
    (11, 0 ),
    (11, 1 ),
    (11, 2 ),
    (11, 5 ),
    (11, 7 );

实验结果如下：

10 | 2-3
11 | 3-4
11 | 6

或

10 | 2
10 | 3
11 | 3
11 | 4
11 | 6

我知道，column'value'的值在0到7之间。
可以用mysql来实现吗？
编辑1
根据我的回答：

SELECT Tbl1.Id,
       startseqno,
       Min(B.Value) - 1 AS END
FROM   (SELECT Id,
               Value + 1 AS StartSeqNo
        FROM   SEQUENCE AS A
        WHERE  NOT EXISTS (SELECT *
                           FROM   SEQUENCE AS B
                           WHERE  B.Id = A.id
                                  AND B.Value = A.Value + 1)
               AND Value < (SELECT Max(Value)
                            FROM   SEQUENCE B
                            WHERE  B.Id = A.Id)) AS Tbl1,
       SEQUENCE AS B
WHERE  B.Id = Tbl1.Id
       AND B.Value > Tbl1.startseqno

但现在我变得

10 | 2 | 3

拜托，有人知道怎么修吗？
sqlfiddle

最佳答案

您可以使用not exists来完成此操作：

select s.*
from sequence s
where not exists (select 1 from sequence s2 where s2.id = s.id and s2.value = s.value + 1) and
      exists (select 1 from sequence s2 where s2.id = s.id and s2.value > s.value);

exists子句很重要，因此您不必为每个id报告最终值。
编辑：
下面是一个更好的方法：

select s.value + 1 as startgap,
       (select min(s2.value) - 1 from sequence s2 where s2.id = s.id and s2.value > s.value) as endgap
from sequence s
where not exists (select 1 from sequence s2 where s2.id = s.id and s2.value = s.value + 1) and
      exists (select 1 from sequence s2 where s2.id = s.id and s2.value > s.value);