select *
from #t
where not((NAME like '%' + A + '%') or (NAME like '%' + B + '%') or (NAME like '%' + C + '%'))
go
print convert(varchar,getdate(),121)
SELECT * from #t where CHARINDEX(A, name)=0 and CHARINDEX(B, name)=0 and CHARINDEX(C, name)=0
go
print convert(varchar,getdate(),121)
go
SELECT * from #t where not ( CHARINDEX(A, name)<>0 or CHARINDEX(B, name)<>0 or CHARINDEX(C, name)<>0)
print convert(varchar,getdate(),121)
go
set nocount off
--原来我那个查询慢得离谱:)
--测试数据生成:
if object_id('tempdb..#t') is not null
drop table #t
go
create table #t (name varchar(80), a varchar(2),b varchar(3), c varchar(4) )
--create clustered index cc on #t (name)
go
insert into #t
select top 100000
nchar(19968+checksum(newid())%10000)+ nchar(19968+checksum(newid())%10000)+ nchar(19968+checksum(newid())%10000),
nchar(19968+checksum(newid())%10000), nchar(19968+checksum(newid())%10000), nchar(19968+checksum(newid())%10000)
from master..sysobjects s,master..sysobjects c
go
select a.*
from TABLE a,TABLE b,TABLE c
where
((a.NAME not like '%' + a.A + '%') and
((b.NAME not like '%' + b.b + '%') and
((c.NAME not like '%' + c.A + '%') and
a.name=b.name and a.name=c.name
select a.*
from TABLE a,TABLE b,TABLE c
where
charindex(a.a,a.name) =0 and
charindex(b.b,b.name) =0 and
charindex(c.c,c.name) =0 and
a.name=b.name and a.name=c.name