Oracle查询多种数据结构并计算合计值

数据情况：

 

 

 

一、造数、建表结构

 

-- auto-generated definition
create table TREETEST
(
    BIZ_DATE         VARCHAR2(8),
    C_ZHDM           VARCHAR2(50),
    PF_NAME          VARCHAR2(100),
    SYMBOL_CODE      VARCHAR2(50),
    CYZC_ID          VARCHAR2(50),
    SEC_CODE_NAME    VARCHAR2(100),
    C_SFZDC          VARCHAR2(8),
    N_CZSL           NUMBER(18, 4),
    N_CZFE           NUMBER(18, 4),
    N_XCCPDWJZ       NUMBER(20, 12),
    PF_SHARE         NUMBER(19, 2),
    AT_TOT_MV        NUMBER(19, 4),
    LIABILITY_TOT_MV NUMBER(19, 4),
    D_CJRQ           VARCHAR2(8),
    C_SCCPDM         VARCHAR2(50)
)
/

comment on table TREETEST is '树Test'
/

comment on column TREETEST.BIZ_DATE is '数据日期 '
/

comment on column TREETEST.C_ZHDM is '产品备案代码（含本层和穿透层）'
/

comment on column TREETEST.PF_NAME is '产品备案名称（含本层和穿透层）'
/

comment on column TREETEST.SYMBOL_CODE is '本层持有证券代码 '
/

comment on column TREETEST.CYZC_ID is '下穿产品备案代码 '
/

comment on column TREETEST.SEC_CODE_NAME is '下穿产品备案名称 '
/

comment on column TREETEST.C_SFZDC is '穿透后是否为最底层 '
/

comment on column TREETEST.N_CZSL is '本层持有数量 '
/

comment on column TREETEST.N_CZFE is '本层持有市值 '
/

comment on column TREETEST.N_XCCPDWJZ is '下穿产品单位净值 '
/

comment on column TREETEST.PF_SHARE is '下穿产品实收资本 '
/

comment on column TREETEST.AT_TOT_MV is '下穿产品总资产 '
/

comment on column TREETEST.LIABILITY_TOT_MV is '下穿产品总负债 '
/

comment on column TREETEST.D_CJRQ is '采集日期 '
/

comment on column TREETEST.C_SCCPDM is '首层产品代码 '
/


INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'a', 'a名', 'e证券代码', 'f', 'e名', 'Y', null, null, null, null, null, null, null, 'a');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'A', 'A名', 'B证券代码', 'B', 'B名', 'N', null, null, null, null, null, null, null, 'A');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'B', 'B名', 'C证券代码', 'C', 'C名', 'Y', null, null, null, null, null, null, null, 'A');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'D', 'D名', 'E证券代码', 'E', 'E名', 'Y', null, null, null, null, null, null, null, 'D');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'a', 'a名', 'b证券代码', 'b', 'b名', 'N', null, null, null, null, null, null, null, 'a');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'b', 'b名', 'c证券代码', 'c', 'c名', 'N', null, null, null, null, null, null, null, 'a');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'c', 'c名', 'd证券代码', 'd', 'd名', 'Y', null, null, null, null, null, null, null, 'a');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'a', 'a名', 'e证券代码', 'e', 'e名', 'Y', null, null, null, null, null, null, null, 'a');
INSERT INTO TREETEST (BIZ_DATE, C_ZHDM, PF_NAME, SYMBOL_CODE, CYZC_ID, SEC_CODE_NAME, C_SFZDC, N_CZSL, N_CZFE, N_XCCPDWJZ, PF_SHARE, AT_TOT_MV, LIABILITY_TOT_MV, D_CJRQ, C_SCCPDM) VALUES ('20230930', 'c', 'c名', 'm证券代码', 'm', 'm名', 'Y', null, null, null, null, null, null, null, 'a');

-- auto-generated definition
create table TTT
(
C_ZHDM VARCHAR2(20),
N_CYSL NUMBER(18, 2),
N_ZFE NUMBER(18, 2)
);


INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('B', 11.00, 110.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('C', 12.00, 120.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('E', 13.00, 130.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('a', 14.00, 140.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('b', 15.00, 150.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('c', 16.00, 160.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('d', 16.00, 160.00);
INSERT INTO TTT (C_ZHDM, N_CYSL, N_ZFE) VALUES ('f', 17.00, 170.00);

 

二、分成查树状图

-- 比较标准的SQL
select a.BIZ_DATE AS BIZ_DATE
     , 'tree' || b.rn  as tree
     , a.C_ZHDM AS C_ZHDM
     , lpad(' ', LEVEL * 2) || PF_NAME       AS PF_NAME
     , LEVEL AS                              levels
     , a.CYZC_ID AS CYZC_ID
     , LTRIM(sys_connect_by_path(a.rn,'-'),'-') as cc
     ,t2.N_CYSL
     , t2.N_ZFE
     ,'(' || LTRIM(SYS_CONNECT_BY_PATH('(' || to_char(NVL(t2.N_CYSL,0)) || '/' || to_char(NVL(t2.N_ZFE,1)) || ')', '*'), '*') || ')*' ||  to_char(NVL(t2.N_ZFE,1)) as str00,
    DBMS_AW.eval_number('(' || LTRIM(SYS_CONNECT_BY_PATH('(' || to_char(NVL(t2.N_CYSL,0)) || '/' || to_char(NVL(t2.N_ZFE,1)) || ')', '*'), '*') || ')*' ||  to_char(NVL(t2.N_ZFE,1))) as sum
from
    (select aa.*,row_number() over (partition by C_ZHDM,C_SCCPDM, LEVEL order by C_ZHDM) as rn from treeTest aa) a
    JOIN (select C_SCCPDM, ROWNUM as rn from (select distinct C_SCCPDM from treeTest order by C_SCCPDM)) b
      ON a.C_SCCPDM = b.C_SCCPDM
    LEFT JOIN ttt t2
      ON a.CYZC_ID = t2.C_ZHDM
start with a.C_ZHDM in (a.C_SCCPDM)
CONNECT BY NOCYCLE a.C_ZHDM = prior a.CYZC_ID
order by tree,CYZC_ID;

查询结果：