22,302
社区成员




create table t1([key] int,name int,flag int)
insert t1
select 1, 1001, 0 union all
select 2, 1002, 1 union all
select 3, 1003, 0 union all
select 4, 1004, 0 union all
select 5, 1005, 1 union all
select 6, 1006, 1 union all
select 7, 1007, 0 union all
select 8, 1008, 0 union all
select 9, 1009, 0 union all
select 10, 1010, 1 union all
select 11, 1011, 1 union all
select 12, 1012, 1
go
;with cte(id,[key],name,flag)
as
(
select ROW_NUMBER()over(order by [key]),* from t1
where flag=0
)
select top 1 [key] ,name,flag from cte
union all
select b.[key],b.name,b.flag from cte a,cte b
where a.id=b.id-1 and a.[key]<>b.[key] -1
/*
(3 行受影响)
key name flag
--- ---- ---
1 1001 0
3 1003 0
7 1007 0
*/
go
drop table t1
create table tab
(Keys int, name int, Flag int)
insert into tab
select 1, 1001, 0 union all
select 2, 1002, 1 union all
select 3, 1003, 0 union all
select 4, 1004, 0 union all
select 5, 1005, 1 union all
select 6, 1006, 1 union all
select 7, 1007, 0 union all
select 8, 1008, 0 union all
select 9, 1009, 0 union all
select 10, 1010, 1 union all
select 11, 1011, 1 union all
select 12, 1012, 1
with pl as
(select Keys,name,Flag,
keys-row_number() over(order by Keys) rn
from tab where Flag=0
)
select *
from tab where keys in
(select min(Keys)
from pl group by rn)
Keys name Flag
----------- ----------- -----------
1 1001 0
3 1003 0
7 1007 0
(3 row(s) affected)
select *
from tabname a
where not exists (
select 1 from tabname b
where b.key = a.key - 1
and b.flag = 0
)
and a.flag = 0