要用到4张表进行多表查询
表1 jw ------ jh x y 3000+记录
表2 djqkgx-------jh qkdyid 3000+记录
表3 qkxx---------- id qkdy 100以内
表4 dba01---------jh rcyl1 rq 42万+
现在已知 qkdy,rq 要查 max(a.x),max(a.y),min(a.x),min(a.y),max(b.RCYL1),min(b.RCYL1)
最初的方案:
select a.max(x),a.max(y),a.min(x),a.min(y),b.max(RCYL1),b.min(RCYL1) from jw a,dba01 b where a.jh in (select jh from djqkgx where qkdyid in (select id from qkxx where qkdy='"+qkdy+"')) and b.jh in(select jh from dba01 where RQ=to_date('"+zb2+"','yyyy-mm-dd')) and a.jh=b.jh
耗时 380S+;
第二方案:分布查询
select max(dba01.rcyl1) from dba01,qkxx,djqkgx where qkxx.qkdy='sss' and qkxx.id = djqkgx.qkdyid and djqkgx.jh = dba01.jh
但只是这一部就耗时400多秒 放弃
最优方案:
select max(a.x),max(a.y),min(a.x),min(a.y),max(b.RCYL1),min(b.RCYL1) from (select jh,x,y from jw where jh in (select jh from djqkgx where qkdyid=(select id from qkxx where qkdy='老两三'))) a ,(select jh,rcyl1 from dba01 where rq=to_date('2001-11-17','yyyy-mm-dd'))b where a.jh=b.jh
就是查出来的数据当做临时表查询,这样数据量大大的减少了 速度 也快多了