2表过滤怎么查啊？应该不难，求解请大侠们。

[zongliang2015]

表1，里面的数据是3,1,0三种结果，随机10行数据如下：
f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14
3 3 0 1 1 3 3 1 1 1 3 3 0 3
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 1 1 1 1 3 0 3
3 1 0 1 3 3 3 1 1 1 3 3 0 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
3 3 0 1 1 1 1 1 1 1 3 3 0 3
表2：过滤条件
000000
111111
33333333
333111
3131313131
33113311
31113111
11113333
13131313
11131113
113311
331133
3113111
1、要求表1里不能含有表2里的列组合，请问怎么写？

[等不到来世]

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
*/

[shildkix]

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 where convert(nvarchar(1),f1)+convert(nvarchar(1),f2)+convert(nvarchar(1),f3)+convert(nvarchar(1),f4)+convert(nvarchar(1),f5)+convert(nvarchar(1),f6)+convert(nvarchar(1),f7)+convert(nvarchar(1),f8)+convert(nvarchar(1),f9)+convert(nvarchar(1),f10)+convert(nvarchar(1),f11)+convert(nvarchar(1),f12)+convert(nvarchar(1),f13)+convert(nvarchar(1),f14) not in(select ft from #tb)

[shildkix]

写错了,前面回复的时候没注意表2的数据是不定长的,这样的话还是要用charindex