mysql - MySql选择孤岛并按顺序删除间隙

我有下表

Id | cont
----------
 1 |  0
 2 |  1
 3 |  0
 4 |  1
 5 |  2
 6 |  3
 7 |  0
 8 |  1
 9 |  2
10 |  0
11 |  1
12 |  2
13 |  3
14 |  4

我需要从2或更高的序列号中获取孤岛，并消除缺口。

结果应该是

Id | cont
----------
 5 |  2
 6 |  3
12 |  2
13 |  3
14 |  4

最佳答案

假设ID将单调增加，则可以通过几个步骤来实现。

首先，对于每个数字，您将获得第一个在前的零

select  t1.ID, t1.cont, max(t2.ID) max_id
from    yourTable t1
join    yourTable t2
on      t1.id >= t2.id
where   t2.cont = 0
group by t1.ID, t1.cont

然后使用它来获得大于一的行大于1的序列

select  t1.max_id, count(*)
from    (
            select  t1.ID, t1.cont, max(t2.ID) max_id /* STEP 1 */
            from    yourTable t1
            join    yourTable t2
            on      t1.id >= t2.id
            where   t2.cont = 0
            group by t1.ID, t1.cont
        ) t1
where   cont > 1
group by t1.max_id
having count(*) > 1

最后加入这两个以获得所需的ID和值

select  t1.id, t1.cont
from    (
            select  t1.ID, t1.cont, max(t2.ID) max_id /* STEP 1 */
            from    yourTable t1
            join    yourTable t2
            on      t1.id >= t2.id
            where   t2.cont = 0
            group by t1.ID, t1.cont

        ) t1
join    (
            select  t1.max_id, count(*) /* STEP 2 */
            from    (
                        select  t1.ID, t1.cont, max(t2.ID) max_id
                        from    yourTable t1
                        join    yourTable t2
                        on      t1.id >= t2.id
                        where   t2.cont = 0
                        group by t1.ID, t1.cont
                    ) t1
            where   cont > 1
            group by t1.max_id
            having count(*) > 1
        ) t2
on      t1.max_id = t2.max_id
where   t1.cont > 1

您可以在操作here中看到此查询