求教各位大神,下面的语句能否简化优化?感觉这样写很死啊
(select '2017-3-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1'
) b
where 1=1 )
union all
(select '2017-6-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1'
) b
where 1=1 )
union all
(select '2017-9-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1'
) b
where 1=1 )
union all
(select '2017-12-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1'
) b
where 1=1 )
(select '2017-3-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1'
) b
where 1=1 )
union all
(select '2017-6-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1'
) b
where 1=1 )
union all
(select '2017-9-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1'
) b
where 1=1 )
union all
(select '2017-12-1' yf, b.*,a.*,case zbsl when 0 then 'ERROR' else to_char(round((zbsl-fbsl)/zbsl*100,2),'fm999999990.999999999')||'%' end cxcl from
(select count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1'
and c.qybm='qqqq'
) a,
(select count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1'
) b
where 1=1 )
(select case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end yf,
count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh and b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1'
and c.qybm='qqqq'
group by case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end
) a,
(select case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end yf,
count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh and b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1'
group by case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end
) b
where 1=1 and a.yf = b.yf;应该还可以优化下,这个你先看看吧,起码语句可读性好很多,减少了扫描表次数
(select case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end yf,
count(distinct a.hh ) zbl,sum(b.scsl) zbsl from da_yhbk a,cb_slb_ls b,da_glszb c
where a.hh=c.hh and a.hh=b.hh
and c.qybm='qqqq'
group by case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end
) a,
(select case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end yf,
count(distinct a.hh) fbl,sum(b.scsl) fbsl from da_yhbk a,cb_slb_ls b
where slqy='qqqq' and a.hh=b.hh
group by case when b.sfyf between add_months(date'2017-3-1',-2) and date'2017-3-1' then '2017-3-1'
when b.sfyf between add_months(date'2017-6-1',-2) and date'2017-6-1' then '2017-6-1'
when b.sfyf between add_months(date'2017-9-1',-2) and date'2017-9-1' then '2017-9-1'
when b.sfyf between add_months(date'2017-12-1',-2) and date'2017-12-1' then '2017-12-1'
end
) b
where 1=1 and a.yf = b.yf;
最终结果,1楼那个有点问题,忘了删条件