求sql想在一个表中查询某个字段连续N条记录是连续+1的前一条记录

求sql想在一个表中查询某个字段连续N条记录是连续+1的前一条记录

表记录 参数num=3(3 条相连(no连续+1)的记录)
id  loclevel          no
1      1              1
2      1              2
3      1              4
4      1              5
5      1              6
6      2              1
7      2              2
8      2              3
9      2              4

我想得到的结果是
id  loclevel        no
3      1              4
6      2              1
7      2              2

也就是说 loclevel是1的情况下 id3到id5 NO字段是3次连续+1的(4,5,6) 所以把id3提出来
        loclevel是2的情况下 id6到id8 NO字段是3次连续+1的(1,2,3)  id7到id9 NO字段是3次连续+1的(2,3,4)
        所以把id6,id7提出来

SQL code--> 测试数据: @s
declare @s table (id int,loclevel int,no int)
insert into @s
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4

select * from @s a
where exists(select 1 from @s where loclevel=a.loclevel and no=a.no+1)
and exists(select 1 from @s where loclevel=a.loclevel and no=a.no+2)

--结果：
id          loclevel    no
----------- ----------- -----------
3           1           4
6           2           1
7           2           2

SQL codedeclare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,5
insert into @t select 5,1,6
insert into @t select 6,2,1
insert into @t select 7,2,2
insert into @t select 8,2,3
insert into @t select 9,2,4

select
    a.*
from
    @t a,@t b,@t c
where
    a.id=b.id-1 and a.no=b.no-1 and a.loclevel=b.loclevel
    and
    a.id=c.id-2 and a.no=c.no-2 and a.loclevel=c.loclevel

SQL codedeclare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,5
insert into @t select 5,1,6
insert into @t select 6,2,1
insert into @t select 7,2,2
insert into @t select 8,2,3
insert into @t select 9,2,4

declare @i int
set @i=3  --当N值变化时，只需替换此处的@i值

select
    a.*
from
    @t a,
    (select t.* from @t t where not exists(select 1 from @t where id=t.id+1 and loclevel=t.loclevel and no=t.no+1)) b
where
    a.id<=b.id and a.loclevel=b.loclevel
group by
    a.id,a.loclevel,a.no
having
    min(b.no)-a.no>=@i-1

/*
id          loclevel    no         
----------- ----------- -----------
3           1           4
6           2           1
7           2           2
*/