SELECT HZ_QYHZNR.ZCH, HZ_QYHZNR.ZS, HZ_QYHZNR.QYMC, HZ_QYHZNR.FDDBR, HZ_QYHZNR.DH
FROM HZ_QYHZNR ,hk_jd WHERE
HZ_QYHZNR.NBXH = hk_jd.NBXH and HZ_QYHZNR.NBXH NOT IN (
SELECT hk_xc_record.NBXH from hk_xc_record WHERE COMPLETED=1 and substrb(hk_xc_record.SYS_DATE,1,4)='2006')
------------------------------------------------------------------------------
上面的SQL语句,查询出结果300多条记录,但需要 9秒时间,速度太慢了。
我知道是 NOT IN 的关系,我也试图用 not exists 写过,可能是我写的不对,速度更慢了。
请高手帮助指点一下,应该怎么写速度比较快!!!!!
FROM HZ_QYHZNR ,hk_jd WHERE
HZ_QYHZNR.NBXH = hk_jd.NBXH and HZ_QYHZNR.NBXH NOT IN (
SELECT hk_xc_record.NBXH from hk_xc_record WHERE COMPLETED=1 and substrb(hk_xc_record.SYS_DATE,1,4)='2006')
------------------------------------------------------------------------------
上面的SQL语句,查询出结果300多条记录,但需要 9秒时间,速度太慢了。
我知道是 NOT IN 的关系,我也试图用 not exists 写过,可能是我写的不对,速度更慢了。
请高手帮助指点一下,应该怎么写速度比较快!!!!!
FROM HZ_QYHZNR ,hk_jd,hk_xc_record
WHERE
HZ_QYHZNR.NBXH = hk_jd.NBXH
and HZ_QYHZNR.NBXH = hk_xc_record.NBXH(+)
AND hk_xc_record.COMPLETED=1
AND substrb(hk_xc_record.SYS_DATE,1,4)='2006'
AND hk_xc_record.NBXH IS NULL
------------------------------
老大,查询出来一条记录也没有!
FROM HZ_QYHZNR ,hk_jd, (select NBXH from hk_xc_record where hk_xc_record.COMPLETED=1 and substrb(hk_xc_record.SYS_DATE,1,4)='2006') hk_xc_record
WHERE
and HZ_QYHZNR.NBXH = hk_xc_record.NBXH(+) AND hk_xc_record.NBXH IS NULL
------------------------------------------------------------------
cenlmmx(学海无涯苦作舟) ,我改了一下你的语句,可以了,谢谢!