求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实现 谢谢了
------解决方案--------------------
表记录 参数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 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 */
------解决方案--------------------
- 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 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 tab a where [no]=(select [no] from tab where a.loclevel=loclevel and ID=a.ID+2)-2 /* id loclevel no ----------- ----------- ----------- 3 1 4 6 2 1 7 2 2 (所影响的行数为 3 行) */
------解决方案--------------------
- SQL code
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-07-09 16:19:14 --------------------------------- --> 生成测试数据表:tb If not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([id] int,[loclevel] int,[no] int) Insert tb 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 Go --Select * from tb -->SQL查询如下: ;with t as ( select rn=row_number() over(order by loclevel,id)-no,* from tb ) select id,loclevel,no from 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 4 6 2 1 7 2 2 (3 行受影响) */
------解决方案--------------------
- SQL code
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- ========================================= IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int,loclevel int ,no int) go insert 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 go declare @s int set @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 4 6 2 1 7 2 2 -------*/