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