sql优化从300秒到7秒

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;

 

sql优化从300秒到7秒

标签：desc   not   des   merge   where   面积   tab   The   code