编辑代码

   
-----------------------------------终止但未清算完毕产品---------------------------  
select round(sum(a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-10-29'
                and SJRQ ='20240930'
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT not in ( '本报告期未终止' )
                                        and b.DIS_DAILY = '2024-10-29' AND SJRQ = '20240930'；
   
   
   
   
   
   
   --------------产品总计，季度环比，季度同比（资产负债的总资产口径算规模）------------------------------------
    select round(sum(a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-10-29'
                and SJRQ ='20240930'
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY = '2024-10-29' AND  SJRQ = '20240930'；
    

    select round(sum(a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-10-29'
                and SJRQ ='20230930'
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY = '2024-10-29' AND  SJRQ = '20230930';
        


        select round(sum(a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-04-17'
                and SJRQ ='20230331'
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY = '2024-04-17' AND  SJRQ = '20230331';

            
------------------------------------------------------------新设立产品-----------------------------------------------------------------------
 select a.JGFDMC, a.CPQC, round((a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where sjrq = '${sjrq}' 
                AND dis_daily  = '${date1}' 
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and '${sjrq}' >= CLRQ >= replace(add_months(to_date('${sjrq}','YYYYMMDD'),-3),'-','')
                                        and b.DIS_DAILY = '${date1}'  AND  SJRQ = '${sjrq}';




------原新设立产品的合计
select '合计' as "当季度新设立产品情况",
        max(CURRENT_PRD_NUM),
        max(CURRENT_PRD_GM),
        max(LAST_QUARTER_PRD_NUM),
        max(LAST_QUARTER_PRD_GM)
from (SELECT COUNT(CPDM) AS CURRENT_PRD_NUM,SUM(GM) AS CURRENT_PRD_GM,NULL AS LAST_QUARTER_PRD_NUM,NULL AS LAST_QUARTER_PRD_GM 
      FROM (SELECT DISTINCT A.cpdm,ROUND(SUM(A.ZXGM)/100000000,2) AS GM
            FROM (select cpdm,zxgm,sjrq 
                  from ZBD_YN_ZXCPJBXXB_D 
                  where  DIS_DAILY  = '${date1}'
                  and sjrq = '${sjrq}'
                  and JGFDMC NOT IN ('中保投资有限责任公司')
                  and CPZZZT = '本报告期未终止'
                union all
                    select cpdm,zxgm,sjrq 
                    from ZBD_YN_ZXCPJBXXB_D 
                    where  DIS_DAILY  = '${date1}'
                    and sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-1),'-','')
                    and JGFDMC NOT IN ('中保投资有限责任公司')
                    and CPZZZT = '本报告期未终止'
                union all
                    select cpdm,zxgm,sjrq 
                    from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
                    and sjrq = replace(add_months(to_date('${sjrq}','YYYYMMDD'),-2),'-','')
                    and JGFDMC NOT IN ('中保投资有限责任公司')
                    and CPZZZT = '本报告期未终止'
                    ) A
       LEFT JOIN (select cpdm,zxgm,sjrq 
	              from ZBD_YN_ZXCPJBXXB_D 
                  where  DIS_DAILY  = '${date1}'
                  and sjrq = REPLACE(TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -2), 'Q') - 1, 'yyyy-mm-dd'), '-', '')
                  and JGFDMC NOT IN ('中保投资有限责任公司')
                  and CPZZZT = '本报告期未终止'
        union all
                select cpdm,zxgm,sjrq 
                from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
                and sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -4), 'yyyymmdd'), '-', '')
                and JGFDMC NOT IN ('中保投资有限责任公司')
                and CPZZZT = '本报告期未终止'
        union all
            select cpdm,zxgm,sjrq 
            from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
            and sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -5), 'yyyymmdd'), '-', '')
            and JGFDMC NOT IN ('中保投资有限责任公司')
            and CPZZZT = '本报告期未终止')
            B ON A.cpdm = B.cpdm
        WHERE B.cpdm IS NULL
        GROUP BY A.CPDM)

UNION ALL

-- 上一季度与上上季度的产品只数的差别  与合计期末规模的变化
SELECT NULL AS CURRENT_PRD_NUM,NULL AS CURRENT_PRD_GM,COUNT(CPDM) AS LAST_QUARTER_PRD_NUM,SUM(GM) AS LAST_QUARTER_PRD_GM FROM 
(SELECT DISTINCT B.cpdm,ROUND(SUM(B.ZXGM)/100000000,2) AS GM
FROM (
    select cpdm,zxgm,sjrq 
	from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
	and sjrq = REPLACE(TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -2), 'Q') - 1, 'yyyy-mm-dd'), '-', '')
    and JGFDMC NOT IN ('中保投资有限责任公司')
    and CPZZZT = '本报告期未终止'
union all
   select cpdm,zxgm,sjrq 
   from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
   and sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -4), 'yyyymmdd'), '-', '')
   and JGFDMC NOT IN ('中保投资有限责任公司')
    and CPZZZT = '本报告期未终止'
union all
   select cpdm,zxgm,sjrq 
   from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
   and sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -5), 'yyyymmdd'), '-', '')
   and JGFDMC NOT IN ('中保投资有限责任公司')
    and CPZZZT = '本报告期未终止'
) B
LEFT JOIN (
    select cpdm,zxgm,sjrq 
    from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
	and sjrq = REPLACE(TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -3), 'Q') - 1, 'yyyy-mm-dd'), '-', '') 
    and JGFDMC NOT IN ('中保投资有限责任公司')
    and CPZZZT = '本报告期未终止'
union all
    select cpdm,zxgm,sjrq 
    from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}' 
	and sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -7), 'yyyymmdd'), '-', '')
    and JGFDMC NOT IN ('中保投资有限责任公司')
    and CPZZZT = '本报告期未终止'
union all
    select cpdm,zxgm,sjrq 
    from ZBD_YN_ZXCPJBXXB_D where  DIS_DAILY  = '${date1}'
	and sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -8), 'yyyymmdd'), '-', '')
    and JGFDMC NOT IN ('中保投资有限责任公司')
    and CPZZZT = '本报告期未终止'
) C ON B.cpdm = C.cpdm
WHERE C.cpdm IS NULL
GROUP BY B.CPDM))
--------------------------表3 产品发行状况----------------------------------------------------

---本季度末余额，资产口径
    select a.JGFDMC, a.CPQC, round((a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-04-17'
                and SJRQ ='20240331'
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY = '2024-04-17' AND  SJRQ = '20240331';
    

---上季度末余额，资产口径
    select a.JGFDMC, a.CPQC, round((a.suma)/100000000,3) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-04-17'
                and SJRQ ='20231231'
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by cpqc, JGFDMC)a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY = '2024-04-17' AND  SJRQ = '20231231';


--------------------------------------------------------------------/*报表配置111--当月，上季度，同比的产品只数和规模*/-------------------------------------------------------
select 
    max(cp_num) as cp_num,
    max(gm_sum) as gm_sum,
    max(last_quarter_cp_num) as last_quarter_cp_num,
    max(last_quarter_gm_sum) as last_quarter_gm_sum,
    max(last_year_quarter_gm_sum) as last_year_quarter_gm_sum
from 
-- 将本季度的产品数量和产品规模插入到临时表中
(
    (SELECT COUNT(cpdm) AS cp_num,null as gm_sum,null as last_quarter_cp_num,null as last_quarter_gm_sum,null as last_year_quarter_gm_sum,null as sjrq
FROM ZBD_YN_ZXCPJBXXB_D
WHERE sjrq = '${sjrq}' 
      AND dis_daily  = '${date1}' 
      AND JGFDMC NOT IN ('中保投资有限责任公司')
      AND CPZZZT = '本报告期未终止'
group by sjrq)

union all (select null as cp_num, round(sum(a.suma)/100000000,2) as gm_sum, null as last_quarter_cp_num,null as last_quarter_gm_sum,null as last_year_quarter_gm_sum,null as sjrq
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily= '${date1}'
                and SJRQ ='${sjrq}' 
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY =  '${date1}' AND  SJRQ = '${sjrq}')
-- 将上季度的产品数量和产品规模插入到临时表中
union all (SELECT
        null as cp_num,
        null as gm_sum,
        COUNT(cpdm) AS last_quarter_cp_num,
        null AS last_quarter_gm_sum,
        null as last_year_quarter_gm_sum,
        null as sjrq
FROM ZBD_YN_ZXCPJBXXB_D
WHERE sjrq = REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -3), 'yyyy-mm-dd'), '-', '') 
      and dis_daily = '${date1}'
      and JGFDMC NOT IN ('中保投资有限责任公司')
      and CPZZZT = '本报告期未终止')

union all (select null as cp_num, null as gm_sum, null as last_quarter_cp_num, round(sum(a.suma)/100000000,2) as last_quarter_gm_sum,null as last_year_quarter_gm_sum,null as sjrq
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          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 ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )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'), '-', '') )
-- 去年同一季度的合计总规模

union all(select null as cp_num, null as gm_sum, null as last_quarter_cp_num,null as last_quarter_gm_sum,round(sum(a.suma)/100000000,2)AS  last_year_quarter_gm_sum, null as sjrq
 from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily= '${date1}'
                and SJRQ =REPLACE(TO_CHAR(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -12),'yyyy-mm-dd'), '-', '') 
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )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'), -12),'yyyy-mm-dd'), '-', '') )
)


----------------------------图1. 开放封闭类型 按资产口径--------------------------------
    select b.KF_FBLX, round(sum(a.suma)/100000000,2) as sum
    from (select sum(QMYE) as suma, JGFDMC, cpqc
          FROM ZBD_YN_ZXCPZCFZB_D
          where dis_daily='2024-10-29'
                and SJRQ ='20240930' 
                and ZCFZLX = '资产'
                and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
          group by JGFDMC, cpqc )a
          left join ZBD_YN_ZXCPJBXXB_D  b on a.CPQC = b.CPQC 
                                  where b.CPZZZT = '本报告期未终止' 
                                        and b.DIS_DAILY = '2024-10-29' AND  SJRQ ='20240930' 
                                  group by KF_FBLX



-----------------------------------表5 持有资产情况---------------------------------------------------
---投资资产持仓明细表
---没跑出来 先excel
select A.CPQC, A.CYZCMC, A.CYZCLX, round(sum(A.QMZMJZ)/10000,2), round(sum(A.TZCB)/10000,2)
from ZBD_YN_ZXCPTZZCCCMXB_D  A
LEFT  JOIN ZBD_YN_ZXCPJBXXB_D B on A.CPQC = B.CPQC 
                                  and a.dis_daily=b.dis_daily 
                                  and a.sjrq=b.sjrq
           where B.CPZZZT = '本报告期未终止' 
                 and A.JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
                 and  A.dis_daily  = '2024-04-17' 
                 and  A.sjrq = '20240331' 
group by A.CPQC, A.CYZCMC, A.CYZCLX


---跑出来了，因为 sum(A.QMZMJZ) awe, sum(A.TZCB) qwerty这个命名
select A.CPQC, A.CYZCMC, A.CYZCLX, round(sum(A.QMZMJZ)/10000,2) awe, round(sum(A.TZCB)/10000,2) qwerty
from ZBD_YN_ZXCPTZZCCCMXB_D  A
LEFT  JOIN ZBD_YN_ZXCPJBXXB_D B on  a.dis_daily=b.dis_daily and a.sjrq=b.sjrq and A.CPQC = B.CPQC
where A.dis_daily  = '2024-04-17'  and  A.sjrq = '20240331'  and B.CPZZZT = '本报告期未终止'
and A.JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
group by A.CPQC, A.CYZCMC, A.CYZCLX

-----------------------------------表6 资产负债-------------------------------------------------------------

select A.ZCFZLX,A.KM, A.ZCFZQYXMC,round(sum(A.QMYE)/100000000,2) ye
from (select * from ZBD_YN_ZXCPZCFZB_D
      where dis_daily = '2024-04-17'
            and sjrq = '20240331'
            and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
      )A
LEFT JOIN ZBD_YN_ZXCPJBXXB_D B on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' 
      and  B.dis_daily  = '2024-04-17' 
      and  B.sjrq = '20240331' 
group by A.ZCFZLX,A.KM, A.ZCFZQYXMC;


----------------------------------------------图4 持有机构类型对应期末持有规模------------------------------------------------------------
select A.CYJGLX, round(sum(QMCYGM)/100000000,2) CYYE
from 
(select * from ZBD_YN_ZXCPCYRFEXXB_D 
where dis_daily  = '2024-04-17' 
      and  sjrq = '20240331' 
      and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
)A
LEFT JOIN ZBD_YN_ZXCPJBXXB_D B on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' 
      and  B.dis_daily  = '2024-04-17' 
      and  B.sjrq = '20240331' 
group by A.CYJGLX



--------------------------------------图5 上市公司属性---------------------------------------------------------------------------------------
select A.SSGSSX, ROUND(SUM(A.QMZMYE)/100000000,2) JE
from (select * from ZBD_YN_ZXCPTZZCCCMXB_D
      where dis_daily = '2024-04-17'
            and sjrq='20240331'
            and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
      ) A
LEFT  JOIN ZBD_YN_ZXCPJBXXB_D B on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' 
      and  B.dis_daily  = '2024-04-17' 
      and  B.sjrq = '20240331' 
GROUP BY A.SSGSSX;



-------------------------------------图6 上市公司所属行业 对应期末账面余额-------------------------------------------------------------------------------------
select A.SSGSSSXY, ROUND(SUM(A.QMZMYE)/100000000,2) JE
from (select * from ZBD_YN_ZXCPTZZCCCMXB_D
      where dis_daily = '2024-04-18'
            and sjrq='20240331'
            and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
      ) A
LEFT  JOIN ZBD_YN_ZXCPJBXXB_D B on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' 
      and  B.dis_daily  = '2024-04-18' 
      and  B.sjrq = '20240331' 
GROUP BY A.SSGSSSXY;



select 
CASE 
WHEN SSGSSSXY like '01%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '02%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '03%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '04%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '05%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '06%' then '采矿业' 
WHEN SSGSSSXY like '07%' then '采矿业' 
WHEN SSGSSSXY like '08%' then '采矿业' 
WHEN SSGSSSXY like '09%' then '采矿业' 
WHEN SSGSSSXY like '10%' then '采矿业' 
WHEN SSGSSSXY like '11%' then '采矿业' 
WHEN SSGSSSXY like '12%' then '采矿业' 
WHEN SSGSSSXY like '13%' then '制造业' 
WHEN SSGSSSXY like '14%' then '制造业' 
WHEN SSGSSSXY like '15%' then '制造业' 
WHEN SSGSSSXY like '16%' then '制造业' 
WHEN SSGSSSXY like '17%' then '制造业' 
WHEN SSGSSSXY like '18%' then '制造业' 
WHEN SSGSSSXY like '19%' then '制造业' 
WHEN SSGSSSXY like '20%' then '制造业' 
WHEN SSGSSSXY like '21%' then '制造业' 
WHEN SSGSSSXY like '22%' then '制造业' 
WHEN SSGSSSXY like '23%' then '制造业' 
WHEN SSGSSSXY like '24%' then '制造业' 
WHEN SSGSSSXY like '25%' then '制造业' 
WHEN SSGSSSXY like '26%' then '制造业' 
WHEN SSGSSSXY like '27%' then '制造业' 
WHEN SSGSSSXY like '28%' then '制造业' 
WHEN SSGSSSXY like '29%' then '制造业' 
WHEN SSGSSSXY like '30%' then '制造业' 
WHEN SSGSSSXY like '31%' then '制造业' 
WHEN SSGSSSXY like '32%' then '制造业' 
WHEN SSGSSSXY like '33%' then '制造业' 
WHEN SSGSSSXY like '34%' then '制造业' 
WHEN SSGSSSXY like '35%' then '制造业' 
WHEN SSGSSSXY like '36%' then '制造业' 
WHEN SSGSSSXY like '37%' then '制造业' 
WHEN SSGSSSXY like '38%' then '制造业' 
WHEN SSGSSSXY like '39%' then '制造业' 
WHEN SSGSSSXY like '40%' then '制造业' 
WHEN SSGSSSXY like '41%' then '制造业' 
WHEN SSGSSSXY like '42%' then '制造业' 
WHEN SSGSSSXY like '43%' then '制造业' 
WHEN SSGSSSXY like '44%' then '电力、热力、燃气及水生产和供应业' 
WHEN SSGSSSXY like '45%' then '电力、热力、燃气及水生产和供应业' 
WHEN SSGSSSXY like '46%' then '电力、热力、燃气及水生产和供应业' 
WHEN SSGSSSXY like '47%' then '建筑业' 
WHEN SSGSSSXY like '48%' then '建筑业' 
WHEN SSGSSSXY like '49%' then '建筑业' 
WHEN SSGSSSXY like '50%' then '建筑业' 
WHEN SSGSSSXY like '51%' then '批发和零售业' 
WHEN SSGSSSXY like '52%' then '批发和零售业' 
WHEN SSGSSSXY like '53%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '54%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '55%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '56%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '57%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '58%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '59%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '60%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '61%' then '住宿和餐饮业'
WHEN SSGSSSXY like '62%' then '住宿和餐饮业' 
WHEN SSGSSSXY like '63%' then '信息传输、软件和信息技术服务业' 
WHEN SSGSSSXY like '64%' then '信息传输、软件和信息技术服务业' 
WHEN SSGSSSXY like '65%' then '信息传输、软件和信息技术服务业' 
WHEN SSGSSSXY like '66%' then '金融业' 
WHEN SSGSSSXY like '67%' then '金融业' 
WHEN SSGSSSXY like '68%' then '金融业' 
WHEN SSGSSSXY like '69%' then '金融业' 
WHEN SSGSSSXY like '70%' then '房地产业' 
WHEN SSGSSSXY like '71%' then '租赁和商务服务业' 
WHEN SSGSSSXY like '72%' then '租赁和商务服务业' 
WHEN SSGSSSXY like '73%' then '科学研究和技术服务业' 
WHEN SSGSSSXY like '74%' then '科学研究和技术服务业' 
WHEN SSGSSSXY like '75%' then '科学研究和技术服务业' 
WHEN SSGSSSXY like '76%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '77%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '78%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '79%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '80%' then '居民服务、修理和其它服务业' 
WHEN SSGSSSXY like '81%' then '居民服务、修理和其它服务业' 
WHEN SSGSSSXY like '82%' then '居民服务、修理和其它服务业' 
WHEN SSGSSSXY like '83%' then '教育' 
WHEN SSGSSSXY like '84%' then '卫生和社会工作' 
WHEN SSGSSSXY like '85%' then '卫生和社会工作' 
WHEN SSGSSSXY like '86%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '87%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '88%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '89%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '90%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '91%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '92%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '93%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '94%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '95%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '96%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '97%' then '国际组织' 
WHEN SSGSSSXY like 'A%' THEN '农、林、牧、渔业'
WHEN SSGSSSXY like 'B%' THEN '采矿业'
WHEN SSGSSSXY like 'C%' THEN '制造业'
WHEN SSGSSSXY like 'D%' THEN '电力、热力、燃气及水生产和供应业'
WHEN SSGSSSXY like 'E%' THEN '建筑业'
WHEN SSGSSSXY like 'F%' THEN '批发和零售业'
WHEN SSGSSSXY like 'G%' THEN '交通运输、仓储和邮政业'
WHEN SSGSSSXY like 'H%' THEN '住宿和餐饮业'
WHEN SSGSSSXY like 'I%' THEN '信息传输、软件和信息技术服务业'
WHEN SSGSSSXY like 'J%' THEN '金融业'
WHEN SSGSSSXY like 'K%' THEN '房地产业'
WHEN SSGSSSXY like 'L%' THEN '租赁和商务服务业'
WHEN SSGSSSXY like 'M%' THEN '科学研究和技术服务业'
WHEN SSGSSSXY like 'N%' THEN '水利、环境和公共设施管理业'
WHEN SSGSSSXY like 'O%' THEN '居民服务、修理和其它服务业'
WHEN SSGSSSXY like 'P%' THEN '教育'
WHEN SSGSSSXY like 'Q%' THEN '卫生和社会工作'
WHEN SSGSSSXY like 'R%' THEN '文化、体育和娱乐业'
WHEN SSGSSSXY like 'S%' THEN '公共管理、社会保障和社会组织'
WHEN SSGSSSXY like 'T%' THEN '国际组织'
WHEN SSGSSSXY like 'Z%' THEN '不适用'
ELSE SSGSSSXY END AS 上市公司所属行业,
SUM(QMZMYE) AS "期末账面余额(万元)",
concat(sum(ZB),'%') AS 占比
FROM 
(select
DISTINCT SSGSSSXY,
round(sum(QMZMYE)/10000,2) as QMZMYE ,
ROUND(SUM(QMZMYE)/(SELECT SUM(QMZMYE) FROM ZBD_YN_ZXCPTZZCCCMXB_D WHERE  DIS_DAILY  = '${date1}' AND  SJRQ  = '${sjrq}')*100,2) AS  ZB
from ZBD_YN_ZXCPTZZCCCMXB_D WHERE  DIS_DAILY  = '${date1}' AND  SJRQ =  '${sjrq}' GROUP BY SSGSSSXY




union all
select
'总计' as SSGSSSXY,
round(sum(QMZMYE)/10000,2) as "期末账面余额(亿元)",
ROUND(SUM(QMZMYE)/(SELECT SUM(QMZMYE) FROM ZBD_YN_ZXCPTZZCCCMXB_D WHERE  DIS_DAILY  = '${date1}' AND  SJRQ  = '${sjrq}')*100,2) AS  占比
from ZBD_YN_ZXCPTZZCCCMXB_D WHERE  DIS_DAILY  = '${date1}' AND  SJRQ =  '${sjrq}' )
GROUP BY CASE 
WHEN SSGSSSXY like '01%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '02%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '03%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '04%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '05%' then '农、林、牧、渔业' 
WHEN SSGSSSXY like '06%' then '采矿业' 
WHEN SSGSSSXY like '07%' then '采矿业' 
WHEN SSGSSSXY like '08%' then '采矿业' 
WHEN SSGSSSXY like '09%' then '采矿业' 
WHEN SSGSSSXY like '10%' then '采矿业' 
WHEN SSGSSSXY like '11%' then '采矿业' 
WHEN SSGSSSXY like '12%' then '采矿业' 
WHEN SSGSSSXY like '13%' then '制造业' 
WHEN SSGSSSXY like '14%' then '制造业' 
WHEN SSGSSSXY like '15%' then '制造业' 
WHEN SSGSSSXY like '16%' then '制造业' 
WHEN SSGSSSXY like '17%' then '制造业' 
WHEN SSGSSSXY like '18%' then '制造业' 
WHEN SSGSSSXY like '19%' then '制造业' 
WHEN SSGSSSXY like '20%' then '制造业' 
WHEN SSGSSSXY like '21%' then '制造业' 
WHEN SSGSSSXY like '22%' then '制造业' 
WHEN SSGSSSXY like '23%' then '制造业' 
WHEN SSGSSSXY like '24%' then '制造业' 
WHEN SSGSSSXY like '25%' then '制造业' 
WHEN SSGSSSXY like '26%' then '制造业' 
WHEN SSGSSSXY like '27%' then '制造业' 
WHEN SSGSSSXY like '28%' then '制造业' 
WHEN SSGSSSXY like '29%' then '制造业' 
WHEN SSGSSSXY like '30%' then '制造业' 
WHEN SSGSSSXY like '31%' then '制造业' 
WHEN SSGSSSXY like '32%' then '制造业' 
WHEN SSGSSSXY like '33%' then '制造业' 
WHEN SSGSSSXY like '34%' then '制造业' 
WHEN SSGSSSXY like '35%' then '制造业' 
WHEN SSGSSSXY like '36%' then '制造业' 
WHEN SSGSSSXY like '37%' then '制造业' 
WHEN SSGSSSXY like '38%' then '制造业' 
WHEN SSGSSSXY like '39%' then '制造业' 
WHEN SSGSSSXY like '40%' then '制造业' 
WHEN SSGSSSXY like '41%' then '制造业' 
WHEN SSGSSSXY like '42%' then '制造业' 
WHEN SSGSSSXY like '43%' then '制造业' 
WHEN SSGSSSXY like '44%' then '电力、热力、燃气及水生产和供应业' 
WHEN SSGSSSXY like '45%' then '电力、热力、燃气及水生产和供应业' 
WHEN SSGSSSXY like '46%' then '电力、热力、燃气及水生产和供应业' 
WHEN SSGSSSXY like '47%' then '建筑业' 
WHEN SSGSSSXY like '48%' then '建筑业' 
WHEN SSGSSSXY like '49%' then '建筑业' 
WHEN SSGSSSXY like '50%' then '建筑业' 
WHEN SSGSSSXY like '51%' then '批发和零售业' 
WHEN SSGSSSXY like '52%' then '批发和零售业' 
WHEN SSGSSSXY like '53%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '54%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '55%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '56%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '57%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '58%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '59%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '60%' then '交通运输、仓储和邮政业' 
WHEN SSGSSSXY like '61%' then '住宿和餐饮业'
WHEN SSGSSSXY like '62%' then '住宿和餐饮业' 
WHEN SSGSSSXY like '63%' then '信息传输、软件和信息技术服务业' 
WHEN SSGSSSXY like '64%' then '信息传输、软件和信息技术服务业' 
WHEN SSGSSSXY like '65%' then '信息传输、软件和信息技术服务业' 
WHEN SSGSSSXY like '66%' then '金融业' 
WHEN SSGSSSXY like '67%' then '金融业' 
WHEN SSGSSSXY like '68%' then '金融业' 
WHEN SSGSSSXY like '69%' then '金融业' 
WHEN SSGSSSXY like '70%' then '房地产业' 
WHEN SSGSSSXY like '71%' then '租赁和商务服务业' 
WHEN SSGSSSXY like '72%' then '租赁和商务服务业' 
WHEN SSGSSSXY like '73%' then '科学研究和技术服务业' 
WHEN SSGSSSXY like '74%' then '科学研究和技术服务业' 
WHEN SSGSSSXY like '75%' then '科学研究和技术服务业' 
WHEN SSGSSSXY like '76%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '77%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '78%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '79%' then '水利、环境和公共设施管理业' 
WHEN SSGSSSXY like '80%' then '居民服务、修理和其它服务业' 
WHEN SSGSSSXY like '81%' then '居民服务、修理和其它服务业' 
WHEN SSGSSSXY like '82%' then '居民服务、修理和其它服务业' 
WHEN SSGSSSXY like '83%' then '教育' 
WHEN SSGSSSXY like '84%' then '卫生和社会工作' 
WHEN SSGSSSXY like '85%' then '卫生和社会工作' 
WHEN SSGSSSXY like '86%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '87%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '88%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '89%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '90%' then '文化、体育和娱乐业' 
WHEN SSGSSSXY like '91%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '92%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '93%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '94%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '95%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '96%' then '公共管理、社会保障和社会组织' 
WHEN SSGSSSXY like '97%' then '国际组织' 
WHEN SSGSSSXY like 'A%' THEN '农、林、牧、渔业'
WHEN SSGSSSXY like 'B%' THEN '采矿业'
WHEN SSGSSSXY like 'C%' THEN '制造业'
WHEN SSGSSSXY like 'D%' THEN '电力、热力、燃气及水生产和供应业'
WHEN SSGSSSXY like 'E%' THEN '建筑业'
WHEN SSGSSSXY like 'F%' THEN '批发和零售业'
WHEN SSGSSSXY like 'G%' THEN '交通运输、仓储和邮政业'
WHEN SSGSSSXY like 'H%' THEN '住宿和餐饮业'
WHEN SSGSSSXY like 'I%' THEN '信息传输、软件和信息技术服务业'
WHEN SSGSSSXY like 'J%' THEN '金融业'
WHEN SSGSSSXY like 'K%' THEN '房地产业'
WHEN SSGSSSXY like 'L%' THEN '租赁和商务服务业'
WHEN SSGSSSXY like 'M%' THEN '科学研究和技术服务业'
WHEN SSGSSSXY like 'N%' THEN '水利、环境和公共设施管理业'
WHEN SSGSSSXY like 'O%' THEN '居民服务、修理和其它服务业'
WHEN SSGSSSXY like 'P%' THEN '教育'
WHEN SSGSSSXY like 'Q%' THEN '卫生和社会工作'
WHEN SSGSSSXY like 'R%' THEN '文化、体育和娱乐业'
WHEN SSGSSSXY like 'S%' THEN '公共管理、社会保障和社会组织'
WHEN SSGSSSXY like 'T%' THEN '国际组织'
WHEN SSGSSSXY like 'Z%' THEN '不适用'
ELSE SSGSSSXY END;

----------------------------------表7 持有人情况--------------------------------------------------------------------------
select A.CYJGLX, A.CYJGQC,A.CPQC,ROUND(SUM(A.QMCYGM)/10000,2) AS GM
 from 
(select * from ZBD_YN_ZXCPCYRFEXXB_D 
where dis_daily  = '2024-04-17' 
      and  sjrq = '20240331' 
      and JGFDMC not in ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司')
)A
LEFT JOIN ZBD_YN_ZXCPJBXXB_D B on A.CPQC = B.CPQC 
where B.CPZZZT = '本报告期未终止' 
      and  B.dis_daily  = '2024-04-17' 
      and  B.sjrq = '20240331' 
group by A.CYJGLX, A.CYJGQC,A.CPQC


-----------------------------------表4 产品净值及变化表-----------------------------------------------------------------------------------
SELECT 
    A.CPQC as 产品名称,
    ROUND(SUM(A.ZXGM)/100000000,4) as "最新规模(亿元)",
    A.zxljjz_now as "本季度累计净值（元/份）",
    B.last_quarter_zxljjz as "上季度末累计净值（元/份）",
    case when A.zxljjz_now  <> 0 
         then ROUND(SUM(A.zxljjz_now - B.last_quarter_zxljjz)/A.zxljjz_now *100,2) else null end AS 净值增长率
FROM 
    (select CPQC, ZXGM, ZXLJJZ as zxljjz_now
     from ZBD_YN_ZXCPJBXXB_D 
     WHERE  DIS_DAILY  = '${date1}' 
     AND  SJRQ  = '${sjrq}'
     and CPZZZT = '本报告期未终止' 
     AND JGFDMC NOT IN ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司'))A
left join
    (select CPQC, ZXGM, ZXLJJZ as last_quarter_zxljjz
     from ZBD_YN_ZXCPJBXXB_D 
     WHERE  DIS_DAILY  = '${date1}' 
     AND  SJRQ  = REPLACE(TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE('${sjrq}', 'yyyymmdd'), -2), 'Q') - 1, 'yyyy-mm-dd'), '-', '')
     and CPZZZT = '本报告期未终止' 
     AND JGFDMC NOT IN ('平安不动产有限公司','平安养老保险股份有限公司','人保投资控股有限公司','中保投资有限责任公司','中国人寿养老保险股份有限公司'))B
     ON A.CPQC = B.CPQC
GROUP BY A.CPQC, A.zxljjz_now, B.last_quarter_zxljjz;