oracle分层汇总数据

Question

如图所示，数据中有三级指标的数据，3级指标是最右面的数据，我想通过三级指标汇总求出二级指标分数，通过二级指标汇总求出一级指标分数，应该怎么写这个sql，第二张图是我写的sql

Answer 1

-- ①、非级联方式：

     WITH zb AS (
                SELECT '16559' zbbm, '' sj_zbbm, 1 zbcj, '一级指标' zbmc FROM dual UNION ALL
                SELECT '16560' zbbm, '' sj_zbbm, 1 zbcj, '一级指标' zbmc FROM dual UNION ALL
                SELECT '16564' zbbm, '16559' sj_zbbm, 2 zbcj, '二级指标' zbmc FROM dual UNION ALL
                SELECT '16565' zbbm, '16560' sj_zbbm, 2 zbcj, '二级指标' zbmc FROM dual UNION ALL
                SELECT '16506' zbbm, '16564' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '16558' zbbm, '16564' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '26548' zbbm, '16565' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '26538' zbbm, '16565' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '16526' zbbm, '16564' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual )
          , zbs AS (
                SELECT a.zbmc zbmc_1, a.zbbm zbbm_1, b.zbmc zbmc_2, b.zbbm zbbm_2, c.zbmc zbmc_3, c.zbbm zbbm_3
                  FROM zb a JOIN zb b ON a.zbbm = b.sj_zbbm AND a.zbcj = 1
                                     JOIN zb c ON b.zbbm = c.sj_zbbm ) --  AND c.zbcj = 3 -- 除非你的层级对照有问题，否则这个条件不需要
          , qz AS (
                SELECT 1 ID, '16506' zbid, 10 fs FROM dual UNION ALL
                SELECT 2 ID, '16506' zbid, 20 fs FROM dual UNION ALL
                SELECT 3 ID, '16506' zbid, 10 fs FROM dual UNION ALL
                SELECT 4 ID, '16558' zbid, 10 fs FROM dual UNION ALL
                SELECT 5 ID, '16558' zbid, 10 fs FROM dual UNION ALL
                SELECT 6 ID, '16526' zbid, 15 fs FROM dual UNION ALL
                SELECT 8 ID, '26548' zbid, 25 fs FROM dual UNION ALL
                SELECT 9 ID, '26548' zbid, 35 fs FROM dual UNION ALL
                SELECT 10 ID, '26538' zbid, 35 fs FROM dual UNION ALL
                SELECT 11 ID, '26538' zbid, 15 fs FROM dual UNION ALL
                SELECT 7 ID, '16526' zbid, 15 fs FROM dual )
     SELECT DISTINCT a.zbmc_1, a.zbbm_1, SUM( b.fs ) OVER( PARTITION BY a.zbbm_1 ) fs_1
          , a.zbmc_2, a.zbbm_2, SUM( b.fs ) OVER( PARTITION BY a.zbbm_1, a.zbbm_2 ) fs_2
          , a.zbmc_3, a.zbbm_3, SUM( b.fs ) OVER( PARTITION BY a.zbbm_1, a.zbbm_2, a.zbbm_3 ) fs_2
       FROM zbs a LEFT JOIN qz b ON a.zbbm_3 = b.zbid
      ORDER BY a.zbbm_1, a.zbbm_2, a.zbbm_3

-- ②、级联方式

    WITH zb AS (
                SELECT '16559' zbbm, '' sj_zbbm, 1 zbcj, '一级指标' zbmc FROM dual UNION ALL
                SELECT '16560' zbbm, '' sj_zbbm, 1 zbcj, '一级指标' zbmc FROM dual UNION ALL
                SELECT '16564' zbbm, '16559' sj_zbbm, 2 zbcj, '二级指标' zbmc FROM dual UNION ALL
                SELECT '16565' zbbm, '16560' sj_zbbm, 2 zbcj, '二级指标' zbmc FROM dual UNION ALL
                SELECT '16506' zbbm, '16564' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '16558' zbbm, '16564' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '26548' zbbm, '16565' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '26538' zbbm, '16565' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual UNION ALL
                SELECT '16526' zbbm, '16564' sj_zbbm, 3 zbcj, '三级指标' zbmc FROM dual )
        , zbs AS (
            SELECT zb.*, CONNECT_BY_ROOT zbbm AS zbbm_3 FROM zb START WITH zbcj = 3 CONNECT BY NOCYCLE PRIOR sj_zbbm = zbbm )
        , qz AS (
                SELECT 1 ID, '16506' zbid, 10 fs FROM dual UNION ALL
                SELECT 2 ID, '16506' zbid, 20 fs FROM dual UNION ALL
                SELECT 3 ID, '16506' zbid, 10 fs FROM dual UNION ALL
                SELECT 4 ID, '16558' zbid, 10 fs FROM dual UNION ALL
                SELECT 5 ID, '16558' zbid, 10 fs FROM dual UNION ALL
                SELECT 6 ID, '16526' zbid, 15 fs FROM dual UNION ALL
                SELECT 8 ID, '26548' zbid, 25 fs FROM dual UNION ALL
                SELECT 9 ID, '26548' zbid, 35 fs FROM dual UNION ALL
                SELECT 10 ID, '26538' zbid, 35 fs FROM dual UNION ALL
                SELECT 11 ID, '26538' zbid, 15 fs FROM dual UNION ALL
                SELECT 7 ID, '16526' zbid, 15 fs FROM dual )
        , tj AS ( 
            SELECT /*+ materialize */ a.zbbm, a.zbmc, a.zbcj, a.sj_zbbm, SUM( b.fs ) fs
              FROM zbs a JOIN qz b ON a.zbbm_3 = b.zbid
             GROUP BY a.zbbm, a.zbmc, a.zbcj, a.sj_zbbm )
    SELECT a.zbmc, a.zbbm, a.fs, b.zbmc, b.zbbm, b.fs, c.zbmc, c.zbbm, c.fs
      FROM tj a JOIN tj b ON a.zbbm = b.sj_zbbm AND a.zbcj = 1
                JOIN tj c ON b.zbbm = c.sj_zbbm --  AND c.zbcj = 3 -- 除非你的层级对照有问题，否则这个条件不需要
     ORDER BY a.zbbm, b.zbbm, c.zbbm