关于数据库SQL语句中not in的速度问题
select facctcode,facctname from laccount where facctcode in(select facctcode from lbalance where fstartbal=0 and fdebit=0 and fcredit=0 and fendbal=0) and facctcode not in(select fkmh from fcwvch)使用not in 访问数据库的速度很慢,请问有没有什么更快的方法??
select facctcode,facctname from laccount where facctcode in(select facctcode from lbalance where fstartbal=0 and fdebit=0 and fcredit=0 and fendbal=0) and facctcode not in(select fkmh from fcwvch)使用not in 访问数据库的速度很慢,请问有没有什么更快的方法??
select facctcode,facctname from laccount t
where exists(select facctcode from lbalance where fstartbal=0 and
fdebit=0 and fcredit=0 and fendbal=0 and facctcode=t.facctcode)
and not exists
(select fkmh from fcwvch where fkmh=t.facctcode)
最主要问题还是你说的not in不好修改的话,改成 NOT EXIST (select 'Z' from fcwvch where fkmh =laccount.facctcode )如果你符合以下条件的话:fcwvch表中有除了fkmh之外非空的字段,假设为:b
可以该成如下:
select facctcode,facctname from laccount l,
(select facctcode from lbalance where fstartbal=0 and fdebit=0 and fcredit=0 and fendbal=0)f, cwvch c
where l.facctcode =f. facctcode
and l.facctcode = c.fkmh(+)
and c.b is null