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 
in