编辑代码

----------------------------------持仓类型分布 -- 权益类产品
    本期未终止，不包括不属于资管公司的平安养老 
select CASE
WHEN reorder = 1 THEN
 '存款'
WHEN reorder = 2 THEN
 '债券'
WHEN reorder = 3 THEN
 '股票'
WHEN reorder = 4 THEN
 '基金'
WHEN reorder = 4.1 THEN
 '其中，债券型基金'
WHEN reorder = 4.2 THEN
 '货币基金'
WHEN reorder = 4.3 THEN
 '混合型基金'
WHEN reorder = 4.4 THEN
 '股票型基金'
WHEN reorder = 4.5 THEN
 '基础设施类基金'
WHEN reorder = 4.6 THEN
 '其他基金'
WHEN reorder = 5 THEN
 '保险资管产品'
WHEN reorder = 5.1 THEN
 '其中，债权投资计划'
WHEN reorder = 5.2 THEN
 '股权投资计划'
WHEN reorder = 5.3 THEN
 '组合类保险资管产品'
WHEN reorder = 6 THEN
 '证券、基金及其子公司资管产品、其他私募机构私募基金'
WHEN reorder = 7 THEN
 '资产支持计划及其他资产证券化产品'
WHEN reorder = 8 THEN
 '信托计划'
WHEN reorder = 9 THEN
 '买入返售金融资产'
WHEN reorder = 10 THEN
 '其他资产'
WHEN reorder = 0 THEN
 '合计'
END AS "资产类型",
 round(QMZMJZ_dq/100000000,2) AS "期末规模（亿元）",
 case when QMZMJZ_dq is null then '-100%' else concat(round(QMZMJZ_dq/QMZMJZ_sq -1 ,4)*100,'%') end  AS "较上月变动",
 concat(round(QMZMJZ_dq/sum(case when reorder = 0 then QMZMJZ_dq end ) over() ,4)*100,'%') AS "占比"
from 
(select reorder,
sum(case when sjrq ='${sjrq}' then QMZMJZ end) as QMZMJZ_dq,
sum(case when sjrq =replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') then QMZMJZ end) as QMZMJZ_sq
from
(select a.sjrq,QMZMJZ,
  case  WHEN ZCLX = '存款' THEN
         1 -- 存款
        WHEN ZCLX = '债券' THEN
         2 -- 债券
        WHEN ZCLX = '股票' THEN
         3 -- 股票
        WHEN ZCLX = '证券投资基金' THEN
         4 -- 基金
        WHEN ZCLX = '保险资管产品' THEN
         5 -- 保险资管产品
        WHEN ZCLX IN ('证券公司及其子公司资管产品', '基金管理公司及其子公司专户', '期货公司及其子公司资管产品', '其它私募机构私募基金') THEN
         6 -- 证券、基金及其子公司资管产品 其他私募基金
        WHEN ZCLX IN ('资产支持计划', '其他资产证券化产品') THEN
         7 -- 资产支持计划及其他资产证券化产品
        WHEN ZCLX = '信托公司资管产品' THEN
         8 -- 信托计划
        WHEN ZCLX = '买入返售金融资产' THEN
         9 -- 买入返售金融资产
        WHEN ZCLX IN ('保险私募基金','衍生品','银行理财','金融资产投资公司资管产品','未上市企业股权','资产收益权','应收账款', '保证金、备付金、证券清算款','其他资产') THEN
         10
        END AS reorder
from 
(select cpqc,sjrq 
from  ZBD_YN_ZHLCPJBXXB_RE
where ( sjrq = '${sjrq}' or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') )
and CPZZZT = '本报告期未终止' and CPLX  ='权益类' and JGFDMC not in ('平安养老保险股份有限公司'  ) ) a
left join
(select *
from ZBD_YN_ZHLCPZCFZMXB_RE
where  sjrq = '${sjrq}' or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') ) b
on a.cpqc =b.cpqc and a.sjrq = b.sjrq 
union all
select a.sjrq,QMZMJZ,
  case  WHEN ZCLX = '证券投资基金' and JJLX = '债券型基金' THEN
        4.1 -- 债券型基金
        WHEN ZCLX = '证券投资基金' and  JJLX = '货币基金' THEN
        4.2 -- 货币基金
        WHEN ZCLX = '证券投资基金' and  JJLX = '混合型基金' THEN
        4.3 -- 混合型基金
        WHEN ZCLX = '证券投资基金' and  JJLX = '股票型基金' THEN
        4.4 -- 股票型基金
		WHEN ZCLX = '证券投资基金' and  JJLX = '基础设施类基金' THEN
        4.5 -- 基础设施类基金
		WHEN ZCLX = '证券投资基金' and  JJLX = '其它' THEN
        4.6 -- 其它基金
        WHEN ZCLX = '保险资管产品' and  BXZGCPLX IN ('基础设施债权计划', '不动产债权计划') THEN
        5.1 -- 债权投资计划
        WHEN ZCLX = '保险资管产品' and  BXZGCPLX = '股权投资计划' THEN
        5.2 -- 股权投资计划
        WHEN ZCLX = '保险资管产品' and BXZGCPLX = '组合类产品' THEN
        5.3 -- 组合类保险资管产品
        END AS reorder
from 
(select cpqc,sjrq 
from  ZBD_YN_ZHLCPJBXXB_RE
where ( sjrq = '${sjrq}' or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') )
and CPZZZT = '本报告期未终止' and CPLX  ='权益类' and JGFDMC not in ('平安养老保险股份有限公司'  )) a
left join
(select *
from ZBD_YN_ZHLCPZCFZMXB_RE
where  sjrq = '${sjrq}' or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') ) b
on a.cpqc =b.cpqc and a.sjrq = b.sjrq 
union all 
select a.sjrq,QMZMJZ,0 AS reorder
from 
(select cpqc,sjrq 
from  ZBD_YN_ZHLCPJBXXB_RE
where ( sjrq = '${sjrq}' or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') )
and CPZZZT = '本报告期未终止' and CPLX  ='权益类' and JGFDMC not in ('平安养老保险股份有限公司'  )) a
left join
(select *
from ZBD_YN_ZHLCPZCFZMXB_RE
where  sjrq = '${sjrq}' or sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','') ) b
on a.cpqc =b.cpqc and a.sjrq = b.sjrq
where  b.ZCFZLX ='资产'  )
where reorder is not null
group by reorder ) c
order by reorder 


---------------权益类组合产品的资产明细表 对应出来----------------------------------------
select * 
from (select * from ZBD_YN_ZHLCPZCFZMXB_RE 
      where SJRQ = '20250430')a
      left join (
          select CPQC,SJRQ
          FROM ZBD_YN_ZHLCPJBXXB_RE
          WHERE SJRQ = '20250430' and CPZZZT = '本报告期未终止' and CPLX  ='权益类' and JGFDMC not in ('平安养老保险股份有限公司'  )
      )b
      on a.CPQC = b.cpqc AND a.sjrq = b.sjrq

---------------混合类组合产品的资产明细表 对应出来----------------------------------------
select * 
from (select * from ZBD_YN_ZHLCPZCFZMXB_RE 
      where SJRQ = '20250430')a
      left join (
          select CPQC,SJRQ
          FROM ZBD_YN_ZHLCPJBXXB_RE
          WHERE SJRQ = '20250430' and CPZZZT = '本报告期未终止' and CPLX  ='混合类' and JGFDMC not in ('平安养老保险股份有限公司'  )
      )b
      on a.CPQC = b.cpqc AND a.sjrq = b.sjrq