sql优化从300秒到7秒
原始sql
select b.jd 街道,b.rglm 楼宇,zzrl 楼宇编号,count(oname) 入楼企业总数, (select count(oname) from ${tablename} where zzrl=楼宇编号 and sfgs='y' ) 工商企业数, (select count(oname) from ${tablename} where zzrl=楼宇编号 and sfsw='y') 税务企业数, (select count(oname) from ${tablename} where zzrl=楼宇编号 and sfsjp='y') 四经普企业数, (select count(oname) from ${tablename} where zzrl=楼宇编号 and sfxs='y') 规上企业数, round(sum(sjss)/10000,2) 三级税收(万元), round(sum(qjss)/10000,2) 区级税收(万元), round(b.jzwmj,2) 建筑面积, round(sum(qjss)/b.jzwmj,2) 单位建筑面积税收产出, (select gshy from ${tablename} where zzrl=楼宇编号 GROUP BY gshy order by round(sum(qjss)/10000,2) desc limit 0,1) 主导行业, (select count(oname) from ${tablename} where zzrl=楼宇编号 and gshy=主导行业 and gshy!='' and gshy is not null) 主导行业企业数量, (select round(sum(sjss)/10000,2) from ${tablename} where zzrl=楼宇编号 and gshy=主导行业 and gshy!='' and gshy is not null) 主导行业三级税收(万元), (select round(sum(qjss)/10000,2) from ${tablename} where zzrl=楼宇编号 and gshy=主导行业 and gshy!='' and gshy is not null) 主导行业区级税收(万元), (select oname from ${tablename} where zzrl=楼宇编号 order by sjss+0 desc limit 0,1) 三级税收排名第一企业名称, (select round(sjss/10000,2) from ${tablename} where oname=三级税收排名第一企业名称) 企业三级税收(万元), (select round(qjss/10000,2) from ${tablename} where oname=三级税收排名第一企业名称) 企业区级税收(万元) from ${tablename} a,b_louyu_base_1183new b where a.zzrl=b.hb_bj_zz and zzrl!='' and zzrl is not null group by zzrl;
优化后sql
select c.街道,c.楼宇, c.入楼企业总数, c.工商企业数, c.税务企业数, c.四经普企业数, c.规上企业数, c.三级税收(万元), c.区级税收(万元), c.建筑面积, c.单位建筑面积税收产出,c.主导行业,( SELECT count( oname ) FROM hb_end_201906051646052_merge_rel WHERE zzrl =楼宇编号 AND gshy =主导行业 AND gshy != '' ) 主导行业企业数量, ( SELECT round( sum( sjss ) / 10000, 2 ) FROM hb_end_201906051646052_merge_rel WHERE zzrl =楼宇编号 AND gshy =主导行业 AND gshy != '' ) 主导行业三级税收(万元), ( SELECT round( sum( qjss ) / 10000, 2 ) FROM hb_end_201906051646052_merge_rel WHERE zzrl =楼宇编号 AND gshy =主导行业 AND gshy != '' AND gshy IS NOT NULL ) 主导行业区级税收(万元), c.三级税收排名第一企业名称, ( SELECT round( sjss / 10000, 2 ) FROM hb_end_201906051646052_merge_rel WHERE oname =三级税收排名第一企业名称) 企业三级税收(万元), ( SELECT round( qjss / 10000, 2 ) FROM hb_end_201906051646052_merge_rel WHERE oname =三级税收排名第一企业名称) 企业区级税收(万元) from (SELECT b.jd 街道, b.rglm 楼宇, zzrl 楼宇编号, count( oname ) 入楼企业总数, ( CASE WHEN sfgs = 'y' THEN count( oname ) END ) 工商企业数, ( CASE WHEN sfsw = 'y' THEN count( oname ) END ) 税务企业数, ( CASE WHEN sfsjp = 'y' THEN count( oname ) END ) 四经普企业数, ( CASE WHEN sfxs = 'y' THEN count( oname ) END ) 规上企业数, round( sum( sjss ) / 10000, 2 ) 三级税收(万元), round( sum( qjss ) / 10000, 2 ) 区级税收(万元), round(b.jzwmj,2) 建筑面积, round(sum( qjss ) / b.jzwmj,2) 单位建筑面积税收产出, ( SELECT gshy FROM hb_end_201906051646052_merge_rel WHERE zzrl =楼宇编号 GROUP BY gshy ORDER BY sum( qjss ) DESC LIMIT 0, 1 ) 主导行业,( SELECT oname FROM hb_end_201906051646052_merge_rel WHERE zzrl =楼宇编号 ORDER BY sjss + 0 DESC LIMIT 0, 1 ) 三级税收排名第一企业名称 FROM hb_end_201906051646052_merge_rel a LEFT JOIN b_louyu_base_1183new b ON a.zzrl = b.hb_bj_zz WHERE a.zzrl != '' GROUP BY a.zzrl) c;