日期:2014-05-17 浏览次数:20456 次
--> 测试数据: @T
declare @T table (A int)
insert into @T
select 1 union all
select 2 union all
select null union all
select 2 union all
select 2 union all
select null union all
select 1 union all
select 1 union all
select null union all
select 3 union all
select 1 union all
select 2 union all
select null union all
select 2 union all
select 1 union all
select null
;with maco as
(
select row_number() over (order by (select 1)) as id,* from @T
),list as (
select
a.id,
isnull(ltrim(a.A),' ')+isnull(ltrim(b.A),' ')+isnull(ltrim(c.A),' ') as col
from maco a
left join maco b on a.id=b.id-1
left join maco c on b.id=c.id-1
)
,m3 as
(
select id from list where col='11 '
)
select A from maco where id not in
(
select id from m3
union all
select id-1 from m3
union all
select id+1 from m3
)
and isnull(A,'')<>1
/*
A
-----------
2
NULL
2
2
NULL
3
2
NULL
2
NULL
(10 row(s) affected)
*/