select ic02.* from ic02 where ic02.field1 not in (select ic02.field1 from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001)
为什么我的not exists不行呢??? minus 是可以的
试试: select ic02.* from ic02 where not exists (select * from (select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001) b where ic01.aac001=b.aae001
试试: select ic02.* from ic02 where not exists (select * from (select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001) b where ic01.aac001=b.aac001 )
从性能方面着想,强烈建议版主用minus方法. 用libin_ftsafe(子陌红尘)的方法: select ic02.* from ic02 minus select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001速度比用not in或not exist快好几倍,又不易出错!!!
select ic02.* from ic02 minus select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001
and SubStr(ic01.aae002,1,4)=ic02.aae001)
minus 是可以的
select ic02.* from ic02 where not exists (select * from
(select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001
and SubStr(ic01.aae002,1,4)=ic02.aae001) b
where ic01.aac001=b.aae001
select ic02.* from ic02 where not exists (select * from
(select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001
and SubStr(ic01.aae002,1,4)=ic02.aae001) b
where ic01.aac001=b.aac001 )
当条件成立,则取ic02的所有记录,当条件不成立则一条记录也取不到
用libin_ftsafe(子陌红尘)的方法:
select ic02.* from ic02
minus
select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001速度比用not in或not exist快好几倍,又不易出错!!!
minus
select ic02.* from ic01,ic02 where ic01.aac001=ic02.aac001 and SubStr(ic01.aae002,1,4)=ic02.aae001