日期:2014-05-18 浏览次数:20491 次
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 */
------解决方案--------------------
--> 测试时间: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 行) */
------解决方案--------------------
--------------------------------- -- 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 行受影响) */
------解决方案--------------------
-- ========================================= -- -----------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 in