编辑代码

select ZCLX,
sum(QMZMJZ_dq),null as c1,
sum(QMZMJZ_sq),null as c2,
sum(QMZMJZ_sj),null as c3,
sum(QMZMJZ_qn)
from 
(select
 a.ZCLX ,a.rn
 ,sum(case when a.sjrq='${sjrq}' then QMZMJZ else 0 end)/100000000 as QMZMJZ_dq
 ,sum(case when a.sjrq=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') then QMZMJZ else 0 end)/100000000 as QMZMJZ_sq
 ,sum(case when a.sjrq=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','') then QMZMJZ else 0 end)/100000000 as QMZMJZ_sj
 ,sum(case when a.sjrq=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-12),'-','') then QMZMJZ else 0 end)/100000000 as QMZMJZ_qn
from 
(select SJRQ,CPQC,QMZMJZ,JGFDMC,
case when ZCLX in ('债券','其他资产证券化产品','资产支持计划') then '债券'
     when CKLX = '同业存单' then '同业存单'
     when ZCLX = '存款' then '现金与银行存款'
     when ZCLX = '股票' then '股票'
     when ZCLX = '证券投资基金' then '公募基金'
     when ZCLX = '未上市企业股权' then '股权'
     when ZCLX = '保险资管产品' then '金融产品'
     else '其他' end as ZCLX,
case when ZCLX in ('债券','其他资产证券化产品','资产支持计划') then 1
     when CKLX = '同业存单' then 1.1
     when ZCLX = '存款' then 2
     when ZCLX = '股票' then 4
     when ZCLX = '证券投资基金' then 5
     when ZCLX = '未上市企业股权' then 6
     when ZCLX = '保险资管产品' then 7
     else 8 end as rn 
 from ZBD_YN_ZHLCPZCFZMXB_RE e 
where (sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','')
or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','')
or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-12),'-','')
or sjrq = '${sjrq}' ) 
and JGFDMC not in ('平安养老保险股份有限公司'  )
and ZCFZLX='资产'
) a
left join  ZBD_YN_ZHLCPJBXXB_RE b
on a.SJRQ =b.SJRQ and a.CPQC = b.CPQC
where  b.CPZZZT = '本报告期未终止' 
group by a.ZCLX,a.rn
union all 
-- 债权
select '非标债权类资产', 3 as rn
 ,sum(case when replace(dis_data_date,'-','')='${sjrq}' then cxgm else 0 end)/100000000 as QMZMJZ_dq
 ,sum(case when replace(dis_data_date,'-','')=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') then cxgm else 0 end)/100000000 as QMZMJZ_sq
 ,sum(case when replace(dis_data_date,'-','')=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','') then cxgm else 0 end)/100000000 as QMZMJZ_sj
 ,sum(case when replace(dis_data_date,'-','')=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-12),'-','') then cxgm else 0 end)/100000000 as QMZMJZ_qn
from ANAL_ZQTZJHJBXXB
where (replace(dis_data_date,'-','') = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','')
or replace(dis_data_date,'-','') = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','')
or replace(dis_data_date,'-','') = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-12),'-','')
or replace(dis_data_date,'-','') = '${sjrq}' )
and MEM_NAME not in
('平安不动产有限公司',
'平安养老保险股份有限公司',
'人保投资控股有限公司',
'中保投资有限责任公司',
'中国人寿养老保险股份有限公司')
union all 
-- 股权
select '股权', 6 as rn
 ,sum(case when replace(dis_data_date,'-','')='${sjrq}' then cxgm else 0 end)/100000000 as QMZMJZ_dq
 ,sum(case when replace(dis_data_date,'-','')=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') then cxgm else 0 end)/100000000 as QMZMJZ_sq
 ,sum(case when replace(dis_data_date,'-','')=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','') then cxgm else 0 end)/100000000 as QMZMJZ_sj
 ,sum(case when replace(dis_data_date,'-','')=replace(add_months(to_date('${sjrq}','YYYYMMDD'),-12),'-','') then cxgm else 0 end)/100000000 as QMZMJZ_qn
from ANAL_GQTZJHJBXXB 
where (replace(dis_data_date,'-','') = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','')
or replace(dis_data_date,'-','') = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','')
or replace(dis_data_date,'-','') = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-12),'-','')
or replace(dis_data_date,'-','') = '${sjrq}' )
and MEM_NAME not in
('平安不动产有限公司',
'平安养老保险股份有限公司',
'人保投资控股有限公司',
'中保投资有限责任公司',
'中国人寿养老保险股份有限公司') ) t
group by ZCLX,rn
order by rn