not in改为 not exists性能大幅度提升

1.使用not in
SELECT b.DEVICE_ASSET_ID
FROM HisDataKWH a,dms_tr_device b 
where a.rq='2009-1-1 0:00:00'
 and a.dev_id=b.tr_id and b.DEVICE_ASSET_ID
not in  (select d.DEVICE_ASSET_ID from HisDataKWH c,dms_tr_device d 
where c.rq='2009-1-1 0:00:00' and c.dev_id=d.tr_id
and (c.zxyg_val is null or d.pt is null or d.ct is null))
group by b.DEVICE_ASSET_ID 
order by b.DEVICE_ASSET_ID

1.使用not exists
select x.* from
(SELECT b.DEVICE_ASSET_ID
FROM HisDataKWH a,dms_tr_device b 
where a.rq='2009-1-1 0:00:00'
 and a.dev_id=b.tr_id ) x
where  not exists (
select 1 from
(select d.DEVICE_ASSET_ID from HisDataKWH c,dms_tr_device d 
where c.rq='2009-1-1 0:00:00' and c.dev_id=d.tr_id
and (c.zxyg_val is null or d.pt is null or d.ct is null)) y
where x.DEVICE_ASSET_ID = y.DEVICE_ASSET_ID
)
group by x.DEVICE_ASSET_ID 
order by x.DEVICE_ASSET_ID

转载于:https://www.cnblogs.com/highmayor/archive/2009/10/23/1588747.html