写的第一个存储过程

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;