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

zongliang2015 2015-09-11 09:52:29

表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里的列组合，请问怎么写？

...全文

90 点赞收藏 3

写回复

3 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

shildkix 2015-09-11

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

回复赞

shildkix 2015-09-11

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)

回复赞

等不到来世 2015-09-11

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

回复赞