22,300
社区成员




if object_id('tempdb.dbo.#ta') is not null drop table #ta
create table #ta (f1 int,f2 int,f3 int,f4 int,f5 int,f6 int,f7 int,f8 int,f9 int,f10 int,f11 int,f12 int,f13 int,f14 int)
insert into #ta
select 3,3,0,1,1,3,3,1,1,1,3,3,0,3 union all
select 3,1,0,3,1,3,3,3,1,1,0,3,0,3 union all
select 3,3,0,1,0,3,3,0,1,3,3,3,0,3 union all
select 1,3,0,1,1,3,3,0,1,1,3,3,3,3 union all
select 3,3,0,1,1,3,3,1,1,1,1,3,0,3 union all
select 3,1,0,1,3,3,3,1,1,1,3,3,0,3 union all
select 3,3,0,1,1,3,3,0,1,1,3,3,1,3 union all
select 0,3,0,3,1,3,3,0,1,1,3,3,0,3 union all
select 3,3,0,1,1,3,3,0,1,1,3,3,0,3 union all
select 3,3,0,1,1,1,1,1,1,1,3,3,0,3
go
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb (ft varchar(1000))
insert into #tb
select '000000' union all
select '111111' union all
select '33333333' union all
select '333111' union all
select '3131313131' union all
select '33113311' union all
select '31113111' union all
select '11113333' union all
select '13131313' union all
select '11131113' union all
select '113311' union all
select '331133' union all
select '3113111'
go
select * from #ta t
where not exists(select 1 from #tb
where CHARINDEX(ft,rtrim(t.f1)+rtrim(t.f2)+rtrim(t.f3)+rtrim(t.f4)+rtrim(t.f5)+rtrim(t.f6)
+rtrim(t.f7)+rtrim(t.f8)+rtrim(t.f9)+rtrim(t.f10)+rtrim(t.f11)+rtrim(t.f12)+rtrim(t.f13)+rtrim(t.f14))>0)
/*
3 1 0 3 1 3 3 3 1 1 0 3 0 3
3 3 0 1 0 3 3 0 1 3 3 3 0 3
1 3 0 1 1 3 3 0 1 1 3 3 3 3
3 3 0 1 1 3 3 0 1 1 3 3 1 3
0 3 0 3 1 3 3 0 1 1 3 3 0 3
3 3 0 1 1 3 3 0 1 1 3 3 0 3
*/