求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实现 谢谢了


[解决办法]

SQL code

declare @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 intset @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)) bwhere    a.id<=b.id and a.loclevel=b.loclevelgroup by    a.id,a.loclevel,a.nohaving     min(b.no)-a.no>=@i-1/*id          loclevel    no          ----------- ----------- ----------- 3           1           46           2           17           2           2*/
[解决办法]
SQL code
--> 测试时间:2009-07-09 16:18:21--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]create table [tab]([id] int,[loclevel] int,[no] int)insert [tab]select 1,1,1 union allselect 2,1,2 union allselect 3,1,4 union allselect 4,1,5 union allselect 5,1,6 union allselect 6,2,1 union allselect 7,2,2 union allselect 8,2,3 union allselect 9,2,4select * from tab a where [no]=(select [no] from tab where a.loclevel=loclevel and ID=a.ID+2)-2/*id          loclevel    no          ----------- ----------- ----------- 3           1           46           2           17           2           2（所影响的行数为 3 行）*/
[解决办法]
SQL code
-----------------------------------  Author: htl258(Tony)--  Date  : 2009-07-09 16:19:14-----------------------------------> 生成测试数据表:tbIf not object_id('[tb]') is null    Drop table [tb]GoCreate table [tb]([id] int,[loclevel] int,[no] int)Insert tbSelect 1,1,1 union allSelect 2,1,2 union allSelect 3,1,4 union allSelect 4,1,5 union allSelect 5,1,6 union allSelect 6,2,1 union allSelect 7,2,2 union allSelect 8,2,3 union allSelect 9,2,4Go--Select * from tb-->SQL查询如下:;with t as(    select rn=row_number() over(order by loclevel,id)-no,*     from tb)select id,loclevel,nofrom t a where exists(    select 1     from t     where rn=a.rn     group by rn     having count(1)>=3)  and no not in(    select top 2 no    from t     where loclevel=a.loclevel    order by no desc)/*id          loclevel    no----------- ----------- -----------3           1           46           2           17           2           2(3 行受影响)*/
[解决办法]
SQL code
-- =========================================-- -----------t_mac 小编-------------   ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tbGOCREATE TABLE tb(id int,loclevel int ,no int)goinsert into tb select 1,1,1 insert into tb select 2,1,2 insert into tb select 3,1,4 insert into tb select 4,1,5 insert into tb select 5,1,6 insert into tb select 6,2,1 insert into tb select 7,2,2 insert into tb select 8,2,3 insert into tb select 9,2,4 godeclare @s intset @s=3--可以任意改select * from tb a where [no]=(select [no] from tb where a.loclevel=loclevel and ID=a.ID+@s-1)-@s+1/*------------3    1    46    2    17    2    2-------*/ 
 
[解决办法]
SQL code
-----------------------------------  Author: htl258(Tony)--  Date  : 2009-07-09 16:19:14-----------------------------------> 生成测试数据表:tbIf not object_id('[tb]') is null    Drop table [tb]GoCreate table [tb]([id] int,[loclevel] int,[no] int)Insert tbSelect 1,1,1 union allSelect 2,1,2 union allSelect 3,1,4 union allSelect 4,1,5 union allSelect 5,1,6 union allSelect 6,2,1 union allSelect 7,2,2 union allSelect 8,2,3 union allSelect 9,2,4 union allSelect 10,2,5 union all --再加两条记录Select 11,2,6Go--Select * from tb-->SQL查询如下:;with t as(    select rn=row_number() over(order by loclevel,id)-no,*     from tb)select id,loclevel,nofrom t a where exists(    select 1     from t     where rn=a.rn     group by rn     having count(1)>=3)  and no not in(    select top 2 no    from t     where loclevel=a.loclevel    order by no desc)/*id          loclevel    no----------- ----------- -----------3           1           46           2           17           2           28           2           39           2           4(5 行受影响)*/
[解决办法]
SQL code
create table lin_test(id  INT,loclevel int,no int)insert into lin_test select 1 ,     1  ,            1 insert into lin_test select 2 ,     1  ,            2 insert into lin_test select 3 ,     1  ,            4 insert into lin_test select 4 ,     1  ,            5 insert into lin_test select 5 ,     1  ,            6 insert into lin_test select 6 ,     2  ,            1 insert into lin_test select 7 ,     2  ,            2 insert into lin_test select 8 ,     2  ,            3 insert into lin_test select 9 ,     2    ,          4 select * from lin_test a where (select count(1) from lin_test b where a.id<b.id and a.loclevel=b.loclevel and b.no<a.no+3)=2godrop table lin_test------------id          loclevel    no          ----------- ----------- ----------- 3           1           46           2           17           2           2（所影响的行数为 3 行）
[解决办法]
SQL code
declare @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 intset @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)) bwhere    a.id<=b.id and a.loclevel=b.loclevelgroup by    a.id,a.loclevel,a.nohaving     min(b.id)-a.id>=@i-1/*id          loclevel    no          ----------- ----------- ----------- 3           1           46           2           17           2           2*/