分区树形递归问题

Question

大家好！遇到一个树形递归问题.测试数据如下:create table TEST_TAB
(
  GP  VARCHAR2(10),
  CID VARCHAR2(10),
  PID VARCHAR2(10),
  VAL NUMBER
)insert into TEST_TAB (GP, CID, PID, VAL)
values ('G1', 'B', null, null);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G1', 'C', 'B', null);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G1', 'D', 'C', null);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G1', 'E', 'D', 100);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G2', 'B', null, null);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G2', 'F', 'B', null);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G2', 'G', 'F', null);
insert into TEST_TAB (GP, CID, PID, VAL)
values ('G2', 'H', 'G', 200);
commit;
1、按GP字段分组,组内为树形结构。本行CID等于下一行PID
2、希望在每个分组内，将每棵树的所有结点的VAL字段值都等于该树的叶子节点的VAL值。期望效果如下：G1  B      100
G1  C   B  100
G1  D   C  100
G1  E   D  100G2  B      200
G2  F   B  200
G2  G   F  200
G2  H   G  200 

Answer 1

好像不用层次查询
SELECT t.gp,
       t.cid,
       t.pid,
       MAX(t.val) over(PARTITION BY t.gp) val
  FROM test_tab t

Answer 2

这样我的测试数据比较懒，就叶子节点的VAL给了值，所以用MAX可以取到。
实际的需要：取得每棵树的叶子节点的VAL。

Answer 3

SQL> select * from test_tab t;GP         CID        PID               VAL
---------- ---------- ---------- ----------
G1         B                            200
G1         C          B                  50
G1         D          C                  40
G1         E          D                 100
G2         B                             20
G2         F          B                 300
G2         G          F                 400
G2         H          G                 2008 rows selected
SQL> SELECT m.gp,
  2         m.cid,
  3         m.pid,
  4         MAX(m.val) KEEP(DENSE_RANK LAST ORDER BY m.leaf) OVER(PARTITION BY m.gp) val
  5    FROM (SELECT t.*,
  6                 CONNECT_BY_ISLEAF leaf
  7            FROM TEST_TAB t
  8           START WITH t.pid IS NULL
  9          CONNECT BY PRIOR t.cid = t.pid
 10                 AND PRIOR t.gp = t.gp) m
 11  ;GP         CID        PID               VAL
---------- ---------- ---------- ----------
G1         B                            100
G1         C          B                 100
G1         D          C                 100
G1         E          D                 100
G2         B                            200
G2         F          B                 200
G2         G          F                 200
G2         H          G                 2008 rows selected

Answer 4

一个分区内多棵树的情况，没有考虑到？GP         CID        PID               VAL
---------- ---------- ---------- ----------
G1         B                            123
G1         C          B                  32
G1         D          C                  12
G1         E          D                 100
G1         M                             45
G1         N          M                  78
G1         O          N                  91
G1         X          O                  18
G2         B                             78
G2         F          B                  45
G2         G          F                  80
G2         H          G                 200

Answer 5

SELECT m.gp,
       m.cid,
       m.pid,
       MAX(m.val) KEEP(DENSE_RANK LAST ORDER BY m.leaf) OVER(PARTITION BY m.gp, m.root) val
  FROM (SELECT t.*,
               connect_by_root t.cid root,
               CONNECT_BY_ISLEAF leaf
          FROM TEST_TAB t
         START WITH t.pid IS NULL
        CONNECT BY PRIOR t.cid = t.pid
               AND PRIOR t.gp = t.gp) m

Answer 6

另外一种解决方案：-- 这个方案SQL要简单些，但是排序不是按照树形结构
SELECT CONNECT_BY_ROOT(GP) GP,
       CONNECT_BY_ROOT(CID) CID, 
       CONNECT_BY_ROOT(PID) PID2, 
       VAL 
  FROM TEST_TAB 
 WHERE CONNECT_BY_ISLEAF = 1 
CONNECT BY PID = PRIOR CID AND GP = PRIOR GP
ORDER BY GP,CID;