一个字符串匹配经常遇到的SQL逻辑问题！经常遇到！

[mingyicz]

假设一个表T1里面存的是这样的记录
CAN
CSX
PEK
SHA
...
例外一个表T2里存的是这样的记录
CTU-CAN-SGN
CAN-PEK
CTU-NRT
SHA-URC
SHA-CSX-LAX
....
现在要查找是:在T2中查找不包含任意一个存在于T1的字符串的记录，比如CTU和NRT都不存在在T1中，则CTU-NRT这种记录就要查找出来
谢谢！

[jinjazz]

select * from t2 where not exists(select 1 from t1 where charindex(t1.f,t2.f)>0)

[SQL77]

[Quote=引用楼主 mingyicz 的帖子:]
假设一个表T1里面存的是这样的记录
CAN
CSX
PEK
SHA
...
例外一个表T2里存的是这样的记录
CTU-CAN-SGN
CAN-PEK
CTU-NRT
SHA-URC
SHA-CSX-LAX
....
现在要查找是:在T2中查找不包含任意一个存在于T1的字符串的记录，比如CTU和NRT都不存在在T1中，则CTU-NRT这种记录就要查找出来
谢谢！
[/Quote]

SELECT * FROM T2 WHERE NOT EXISTS(SELECT * FROM T1 WHERE CHARIDENX(T1字段,T2字段)>0)

[SQL77]

SELECT * FROM T2 WHERE NOT EXISTS(SELECT * FROM T1 WHERE CHARINEX(T1字段,T2字段)>0)

[jinjazz]

declare @t1 table(f varchar(100))
insert into @t1 select 'CAN'
insert into @t1 select 'CSX'
insert into @t1 select 'PEK'
insert into @t1 select 'SHA' 

declare @t2 table(f varchar(100))
insert into @t2 select 'CTU-CAN-SGN'
insert into @t2 select 'CAN-PEK'
insert into @t2 select 'CTU-NRT'
insert into @t2 select 'SHA-URC' 
insert into @t2 select 'SHA-CSX-LAX' 
 
select * from @t2 a where not exists(select 1 from @t1 where charindex(f,a.f)>0)

--CTU-NRT

[SQL77]

郁闷呀又打错单词了!稍微改下

SELECT * FROM T2 WHERE NOT EXISTS(SELECT * FROM T1 WHERE CHARINDEX(LTRIM(T1字段),LTRIM(T2字段))>0)

[nzperfect]

--> 测试数据: #T1
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (cl1 varchar(3))
insert into #T1
select 'CAN' union all
select 'CSX' union all
select 'PEK' union all
select 'SHA'
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (cl2 varchar(11))
insert into #T2
select 'CTU-CAN-SGN' union all
select 'CAN-PEK' union all
select 'CTU-NRT' union all
select 'SHA-URC' union all
select 'SHA-CSX-LAX'

select * from #T2 where not exists(select 1 from #T1 where #T2.cl2 like '%'+cl1+'%')
/*
CTU-NRT
*/

[wanshichen]

[Quote=引用 1 楼 jinjazz 的回复:]
select * from t2 where not exists(select 1 from t1 where charindex(t1.f,t2.f)>0)
[/Quote]
支持一下

[--小F--]

select * from T1 where not exists(select 1 from T1 where charindex(T1.f,T2.f)>0)

[--小F--]

[Quote=引用 8 楼 fredrickhu 的回复:]
SQL codeselect * from T1 where not exists(select 1 from T1 where charindex(T1.f,T2.f)>0)
[/Quote]
修改

select * from T2 where not exists(select 1 from T1 where charindex(T1.f,T2.f)>0)

[claro]

帮顶

[JonasFeng]

如剪剪所说：

SELECT * FROM T2 WHERE NOT EXISTS(SELECT * FROM T1 WHERE CHARIDENX(T1字段,T2字段)>0)

[yanleiyigan]

lg

[mingyicz]

哦
我一直以来对EXISTS的用法的理解：如果EXISTS后面能返回至少一行，则EXISTS相当于TRUE，否则相当与FALSE，如if exists....
一直对EXISTS的用法理解不是很透彻 希望高手明示