大家看看这段代码可以那些地方可以优化一下?
select table1.*,sfz.dq,A.min,T1.rate as ratemin,A.max,T2.rate as ratemax,T3.gjj_rate as gjj_ratemin,B.gjjmin,T4.gjj_rate as gjj_ratemax,B.gjjmax from table1
left join ( select ygid, min(changedate) as min, max(changedate) as max from sb_rate group by ygid
) A on table1.id=A.ygid
left join sb_rate T1
on T1.ygid=A.ygid and T1.changedate=A.min
left join sb_rate T2
on T2.ygid=A.ygid and T2.changedate=A.max
left join (select ygid,min(changedate) as gjjmin,max(changedate) as gjjmax from gjj_rate group by ygid
) B
on table1.id=B.ygid
left join gjj_rate T3
on T3.ygid=B.ygid and T3.changedate=B.gjjmin
left join gjj_rate T4
on T4.ygid=B.ygid and T4.changedate=B.gjjmax
left join sfz on left(id_card,6)=sfz.bm
[解决办法]
--子查询试试,再合理建索引
select table1.* ,
dq=(select bm from sfz where bm=left(table1.id_card,6)),
min=(select min(changedate) from sb_rate where ygid=table1.id),
ratemin=(select rate from sb_rate where ygid=table1.id and changedate=(select min(changedate) from sb_rate where ygid=table1.id)),
max=(select max(changedate) from sb_rate where ygid=table1.id),
ratemax=(select rate from sb_rate where ygid=table1.id and changedate=(select max(changedate) from sb_rate where ygid=table1.id)),
gjj_ratemin=(select gjj_rate from gjj_rate where ygid=table1.id and changedate=(select min(changedate) from gjj_rate where ygid=table1.id)),
gjjmin=(select min(changedate) from gjj_rate where ygid=table1.id ),
gjj_ratemax=(select gjj_rate from gjj_rate where ygid=table1.id and changedate=(select max(changedate) from gjj_rate where ygid=table1.id)),
gjjmax=(select max(changedate) from gjj_rate where ygid=table1.id )
from table1
