表记录 参数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值变化时，[email protected]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>[email protected]/*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 [email protected])[email protected]+1/*------------3 1 46 2 17 2 2-------*/