求想在一个表中查询某个字段连续条物业

求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=clevel and no=+1)

and exists(select 1 from @s where loclevel=clevel and 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

=-1 and =-1 and clevel=clevel

and

=-2 and =-2 and clevel=clevel

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=+1 and loclevel=clevel and no=+1)) b

where

= and clevel=clevel

group by

,clevel,

having

min()=@i-1

/*

id loclevel no

----------- ----------- -----------

3 1 4

6 2 1

7 2 2

同比下降16% */