编辑代码

-- zx_05  表3专项产品发行设立情况
select dense_rank()over(order by A.jgfdmc) as 序号, 
       A.jgfdmc as 产品管理机构, 
       A.cpqc as 产品全称, 
       A.clrq as 成立日期, 
       A.zxgm as "本季度末余额（亿元）", 
       B.last_quarter_zxgm as "上一季度末余额（亿元）",
	   case when B.last_quarter_zxgm <> 0 then  round(sum(A.zxgm-B.last_quarter_zxgm)/B.last_quarter_zxgm*100,2) else null end as "变动(%)"
from 
-- 本季度的最新规模
(select round(sum(A.QMYE)/100000000,3) as zxgm, A.JGFDMC, A.cpqc, B.clrq
FROM 
(select QMYE,JGFDMC, cpqc
FROM ZBD_YN_ZXCPZCFZB_D 
where dis_daily= '${date1}' and SJRQ = '${sjrq}' and ZCFZLX = '资产'
and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司'))A
left join ZBD_YN_ZXCPJBXXB_D  B 
on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' and B.DIS_DAILY = '${date1}' AND  B.SJRQ = '${sjrq}'
GROUP BY A.JGFDMC, A.cpqc, B.clrq)A
LEFT  JOIN (
select A.jgfdmc, A.cpqc, B.clrq, round(sum(A.QMYE)/100000000,3) as last_quarter_zxgm
FROM
(SELECT jgfdmc, cpqc,QMYE
FROM ZBD_YN_ZXCPZCFZB_D
where dis_daily= '${date1}'
and SJRQ = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -3), 'yyyy-mm-dd'), '-', '')
and ZCFZLX = '资产'
and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司'))A
left join ZBD_YN_ZXCPJBXXB_D  B 
on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' and B.DIS_DAILY = '${date1}' 
AND  B.SJRQ = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -3), 'yyyy-mm-dd'), '-', '')
GROUP BY A.jgfdmc, A.cpqc, B.clrq)B
ON A.jgfdmc=B.jgfdmc and A.cpqc = B.cpqc and A.clrq = B.clrq
group by A.jgfdmc,A.cpqc,A.clrq,A.zxgm,B.last_quarter_zxgm;


-- zx_12  表7专项产品持有人情况表
with t1 as(
SELECT A.CYJGLX, A.CYJGQC, A.CPQC, A.qmcygm_now,B.last_quarter_qmcygm, SUM(A.qmcygm_now - B.last_quarter_qmcygm) AS bjdbd
FROM (SELECT  A.CYJGLX,A.CYJGQC,A.CPQC,A.qmcygm_now,A.last_quarter_qmcygm,A.CYRZH 
      FROM (select CYJGLX,CYJGQC,CPQC,round(QMCYGM/10000,2) as qmcygm_now,null as last_quarter_qmcygm,CYRZH
            from ZBD_YN_ZXCPCYRFEXXB_D 
            WHERE DIS_DAILY  = '${date1}' 
                  AND  SJRQ  = '${sjrq}' 
                  AND JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司'))A
            left join ZBD_YN_ZXCPJBXXB_D B ON A.CPQC = B.CPQC
            WHERE B.CPZZZT = '本报告期未终止' 
                  and B.DIS_DAILY = '${date1}' 
                  and SJRQ = '${sjrq}')A
      left join
      (SELECT A.CYJGLX,A.CYJGQC,A.CPQC,A.qmcygm_now,A.last_quarter_qmcygm,A.CYRZH  
       FROM (select CYJGLX,CYJGQC,CPQC,null as qmcygm_now,round(QMCYGM/10000,2) as last_quarter_qmcygm,CYRZH 
             from ZBD_YN_ZXCPCYRFEXXB_D 
             WHERE DIS_DAILY  = '${date1}'
                  AND SJRQ  = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -3), 'yyyy-mm-dd'), '-', '') 
                  AND JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司'))A
             LEFT JOIN ZBD_YN_ZXCPJBXXB_D B ON A.CPQC = B.CPQC
                      WHERE B.CPZZZT = '本报告期未终止' 
                            and B.DIS_DAILY = '${date1}'
                            and SJRQ = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -3), 'yyyy-mm-dd'), '-', ''))  B
       ON A.CYJGLX = B.CYJGLX AND  A.CYJGQC = B.CYJGQC AND A.CPQC = B.CPQC AND A.CYRZH = B.CYRZH 
       GROUP BY A.CYJGLX,A.CYJGQC,A.CPQC,A.qmcygm_now,B.last_quarter_qmcygm
       order by A.CYJGLX
        ),
t2 as (
SELECT '保险集团公司及保险控股公司' as CYJGLX, null as CYJGQC,null as CPQC,sum(t1.qmcygm_now) as qmcygm_now,
        sum(case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end) as last_quarter_qmcygm,
        SUM(t1.qmcygm_now-t1.last_quarter_qmcygm) AS bjdbd
from t1 where cyjglx = '保险集团公司及保险控股公司'
union all
SELECT '保险资产管理公司' as CYJGLX,null as CYJGQC,null as CPQC,sum(t1.qmcygm_now) as qmcygm_now,
        sum(case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end) as last_quarter_qmcygm,
        SUM(t1.qmcygm_now-t1.last_quarter_qmcygm) AS bjdbd
from t1 where cyjglx = '保险资产管理公司' 
union all
SELECT '财产保险公司' as CYJGLX,null as CYJGQC,null as CPQC,sum(t1.qmcygm_now) as qmcygm_now,
       sum(case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end) as last_quarter_qmcygm,
       SUM(t1.qmcygm_now-t1.last_quarter_qmcygm) AS bjdbd
from t1 where cyjglx = '财产保险公司'
union all
SELECT '证券公司' as CYJGLX,null as CYJGQC,null as CPQC,sum(t1.qmcygm_now) as qmcygm_now,
        sum(case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end) as last_quarter_qmcygm,
        SUM(t1.qmcygm_now-t1.last_quarter_qmcygm) AS bjdbd
from t1 where cyjglx = '证券公司' 
union all
SELECT '人身保险公司' as CYJGLX,null as CYJGQC,null as CPQC,sum(t1.qmcygm_now) as qmcygm_now,
        sum(case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end) as last_quarter_qmcygm,
        SUM(t1.qmcygm_now-t1.last_quarter_qmcygm) AS bjdbd
from t1 where cyjglx = '人身保险公司' 
union all
SELECT '其它' as CYJGLX,null as CYJGQC,null as CPQC,sum(t1.qmcygm_now) as qmcygm_now
        sum(case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end) as last_quarter_qmcygm,
        SUM(t1.qmcygm_now-t1.last_quarter_qmcygm) AS bjdbd
from t1 where cyjglx = '其它' 
union all
SELECT t1.CYJGLX,t1.CYJGQC,t1.CPQC,t1.qmcygm_now,
        case when t1.last_quarter_qmcygm is null then 0 else t1.last_quarter_qmcygm end as last_quarter_qmcygm,
        t1.qmcygm_now-t1.last_quarter_qmcygm AS bjdbd
from t1 
        )
select CYJGLX,CYJGQC,CPQC,qmcygm_now,last_quarter_qmcygm,bjdbd 
from t2  
order by CYJGLX,CPQC;