BEGIN
DELETE FROM DM.TM_JZYX_SY_ZYPPSYXS_GX_TMP;
insert INTO DM.TM_JZYX_SY_ZYPPSYXS_GX_TMP(NY,SJDM,SJDMJC,XMID,XMMC,SYXL,XSJE,DXJG)
with tmp as (SELECT NY,SJDM,SJDMJC,PPBS xmid,PPMC xmmc,JLBS,SYXL,XSJE,GGBS FROM DM.TM_JZYX_QGSYXLMX),
tmp1 as (SELECT GGDM,PFJ,KSRQ,JSRQ FROM EDW.TC_JGML WHERE PFJ >= 400 AND JGBS = '元/条'),
tmp4 as (SELECT NY,SJDM,SJDMJC,concat(PPBS,right(ggbs,3)) xmid,concat(PPMC,SUBSTR(ggmc,5)) xmmc,JLBS,SYXL,XSJE,GGBS FROM DM.TM_JZYX_QGSYXLMX WHERE PPMC='真龙'),
tmp2 AS
(select NY,SJDM,SJDMJC,'1154_123' xmid,'其中：三类烟以上' xmmc,sum(case when SYXL is null then 0 else syxl end) syxl,sum(case when XSJE is null then 0 else xsje end) xsje
from tmp
where xmmc='真龙'and (JLBS = '1' Or JLBS = '2' Or JLBS = '3')
group by NY,SJDM,SJDMJC,xmid,xmmc
union all
(select NY,SJDM,SJDMJC,xmid,xmmc,sum(case when SYXL is null then 0 else syxl end) syxl,sum(case when XSJE is null then 0 else xsje end) xsje
from tmp4
group by NY,SJDM,SJDMJC,xmid,xmmc)
union all
(select NY,'000'SJDM,'全国'SJDMJC,xmid,xmmc,sum(case when SYXL is null then 0 else syxl end) syxl,sum(case when XSJE is null then 0 else xsje end) xsje
from tmp4
group by NY,SJDM,SJDMJC,xmid,xmmc)
union all
(select NY,'000'SJDM,'全国'SJDMJC,'1154_123'xmid,'其中：三类烟以上'xmmc,sum(case when SYXL is null then 0 else syxl end) syxl,sum(case when XSJE is null then 0 else xsje end) xsje
from tmp
where xmmc='真龙'AND (JLBS = '1' Or JLBS = '2' Or JLBS = '3')
group by NY,SJDM,SJDMJC,xmid,xmmc)
union all
(select a.ny ny,a.sjdm sjdm,a.SJDMJC SJDMJC,a.xmid xmid,a.xmmc xmmc,sum(case when a.syxl is null then 0 else a.syxl end)syxl,sum(case when a.xsje is null then 0 else a.xsje end) xsje from
(select NY,SJDM,SJDMJC,'1154_400' xmid, '其中：400元/条以上' xmmc, syxl,xsje,
ggbs,concat(ny,'-01') ny2 from tmp
where xmmc='真龙')a
inner join tmp1 b on a.ggbs = b.ggdm
where a.NY2 >= b.KSRQ AND a.NY2 <= b.JSRQ
group by a.ny,a.sjdm,a.SJDMJC,a.xmid,a.xmmc)
union all
(select a.ny ny,a.sjdm sjdm,a.SJDMJC SJDMJC,a.xmid xmid,a.xmmc xmmc,sum(case when a.syxl is null then 0 else a.syxl end)syxl,sum(case when a.xsje is null then 0 else a.xsje end) xsje from
(select NY,'000' SJDM,'全国' SJDMJC,'1154_400' xmid, '其中：400元/条以上' xmmc, syxl,xsje,
ggbs,concat(ny,'-01') ny2 from tmp
where xmmc='真龙')a
inner join tmp1 b on a.ggbs = b.ggdm
where a.NY2 >= b.KSRQ AND a.NY2 <= b.JSRQ
group by a.ny,a.sjdm,a.SJDMJC,a.xmid,a.xmmc)
),
tmp3 as (SELECT
NY,SJDM,SJDMJC, xmid,xmmc,sum(case when SYXL is null then 0 else syxl end) syxl,sum(case when XSJE is null then 0 else xsje end) xsje from tmp2
group by NY,SJDM,SJDMJC, xmid,xmmc)
select case when ny is null then '' else ny end ny,case when sjdm is null then '' else sjdm end sjdm,SJDMJC,case when xmid is null then '' else xmid end xmid,xmmc,syxl,xsje,
case when SYXL <> 0 then XSJE*1.0000/(SYXL*10000) else null end dxjg from TMP3
where sjdm is not null;
END;
网友评论