select * from (
select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as xtsum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as xtnum,'信托计划' as prd from ZBD_YW_BXJGCYXTJHXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CYZT='持有')xt
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as cksum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as cknum,'存款证实书' prd from ZBD_YW_BXJGCYCKZSSXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CYZT='持有')ck on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as xdsum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as xdnum,'信贷资产支持证券' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='信贷资产支持证券' and CYZT='持有')xd on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as qssum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as qsnum,'券商资产支持专项计划' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='券商资产支持专项计划' and CYZT='持有')qs on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as pjsum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as pjnum,'资产支持票据' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='资产支持票据' and CYZT='持有')pj on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as qtsum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as qtnum,'其它' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='其它' and CYZT='持有')qt on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as lcsum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as lcnum,'理财产品' prd from ZBD_YW_BXJGCYLCCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CYZT='持有')lc on 1=1
left join (select round(sum(case when ZMYEZRMB=9999999999 then 0 else ZMYEZRMB end)/100000000,2) as jwsum_ye,count(case when ZMJZ<>0 and ZMYEZRMB<>0 then ZMYEZRMB end) as jwnum,'境外不动产' prd from ZBD_YW_BXJGCYJWBDCXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}')jw on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as tzsum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as tznum,'投资性不动产' prd from ZBD_YW_BXJGCYJNBDCXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and TZLX = '投资性') tz on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as zysum_ye,count(case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as zynum,'自用性不动产' prd from ZBD_YW_BXJGCYJNBDCXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and TZLX = '自用性')zy on 1=1
left join (select round(sum(CXGM)/100000000,2) zqsum,count(distinct C_PRDREGNO) zqnum,'债权投资计划' prd from ANAL_ZQTZJHJBXXB where CXGM<>0 and dis_daily =to_char('${sjrq}','yyyy-MM-dd'))zq on 1=1
left join (select round(sum(CXGM)/100000000,2) gqsum,count(distinct C_PRDREGNO) gqnum,'股权投资计划' prd from ANAL_GQTZJHJBXXB where CXGM<>0 and dis_daily =to_char('${sjrq}','yyyy-MM-dd'))gq on 1=1
left join (select round(sum(CXGM)/100000000,2) zcsum,count(distinct C_PRDREGNO) zcnum,'资产支持计划' prd from ANAL_ZCZCJHJBXXB where CXGM<>0 and dis_daily =to_char('${sjrq}','yyyy-MM-dd'))zc on 1=1
left join (select round(sum(case when QMJZCYE=9999999999 then 0 else QMJZCYE end)/100000000,2) zhsum,count(*) as zhnum,'组合类资管产品' prd from ZBD_YN_ZHLCPJBXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CPZZZT='本报告期未终止')zh on 1=1
left join (select round(sum(case when ZXGM=9999999999 then 0 else ZXGM end)/100000000,2) zxsum,count(*) as zxnum,'专项产品' prd from ZBD_YN_ZXCPJBXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CPZZZT='本报告期未终止')zx on 1=1
***发现 业外产品没有去重
***专项和组合类产品是否需要去重
select * from (
select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as xtsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as xtnum,'信托计划' as prd from ZBD_YW_BXJGCYXTJHXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CYZT='持有')xt
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as cksum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as cknum,'存款证实书' prd from ZBD_YW_BXJGCYCKZSSXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CYZT='持有')ck on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as xdsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as xdnum,'信贷资产支持证券' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='信贷资产支持证券' and CYZT='持有')xd on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as qssum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as qsnum,'券商资产支持专项计划' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='券商资产支持专项计划' and CYZT='持有')qs on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as pjsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as pjnum,'资产支持票据' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='资产支持票据' and CYZT='持有')pj on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as qtsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as qtnum,'其它' prd from ZBD_YW_BXJGCYZCZQHCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and xmlb='其它' and CYZT='持有')qt on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as lcsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as lcnum,'理财产品' prd from ZBD_YW_BXJGCYLCCPXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CYZT='持有')lc on 1=1
left join (select round(sum(case when ZMYEZRMB=9999999999 then 0 else ZMYEZRMB end)/100000000,2) as jwsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYEZRMB<>0 then ZMYEZRMB end) as jwnum,'境外不动产' prd from ZBD_YW_BXJGCYJWBDCXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}')jw on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as tzsum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as tznum,'投资性不动产' prd from ZBD_YW_BXJGCYJNBDCXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and TZLX = '投资性') tz on 1=1
left join (select round(sum(case when ZMYE=9999999999 then 0 else ZMYE end)/100000000,2) as zysum_ye,count(DISTINCT case when ZMJZ<>0 and ZMYE<>0 then ZMYE end) as zynum,'自用性不动产' prd from ZBD_YW_BXJGCYJNBDCXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and TZLX = '自用性')zy on 1=1
left join (select round(sum(CXGM)/100000000,2) zqsum,count(distinct C_PRDREGNO) zqnum,'债权投资计划' prd from ANAL_ZQTZJHJBXXB where CXGM<>0 and dis_daily =to_char('${sjrq}','yyyy-MM-dd'))zq on 1=1
left join (select round(sum(CXGM)/100000000,2) gqsum,count(distinct C_PRDREGNO) gqnum,'股权投资计划' prd from ANAL_GQTZJHJBXXB where CXGM<>0 and dis_daily =to_char('${sjrq}','yyyy-MM-dd'))gq on 1=1
left join (select round(sum(CXGM)/100000000,2) zcsum,count(distinct C_PRDREGNO) zcnum,'资产支持计划' prd from ANAL_ZCZCJHJBXXB where CXGM<>0 and dis_daily =to_char('${sjrq}','yyyy-MM-dd'))zc on 1=1
left join (select round(sum(case when QMJZCYE=9999999999 then 0 else QMJZCYE end)/100000000,2) zhsum,count(*) as zhnum,'组合类资管产品' prd from ZBD_YN_ZHLCPJBXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CPZZZT='本报告期未终止')zh on 1=1
left join (select round(sum(case when ZXGM=9999999999 then 0 else ZXGM end)/100000000,2) zxsum,count(*) as zxnum,'专项产品' prd from ZBD_YN_ZXCPJBXXB_D where sjrq ='${sjrq}' and dis_daily ='${date1}' and CPZZZT='本报告期未终止')zx on 1=1