title

sqltest

CREATE TABLE treeNodes
( 
    ID   VARCHAR2(200 BYTE), /* 節點id */
    NAME VARCHAR2(200 BYTE), /* 節點名稱 */
    UPID VARCHAR2(200 BYTE)  /* 父節點id */
); 


INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('1', 'Root', ''); 
INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('2', 'A', '1'); 
INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('3', 'B', '1'); 
INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('4', 'C', '2'); 
INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('5', 'D', '3'); 
INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('6', 'E', '4');
INSERT INTO treeNodes (ID, NAME, UPID) VALUES ('7', 'F', '6');

語法

 with tmp(id, name, upid, id_path, name_path) as (
      select id,
             name,
             upid,
             id as id_path,
             name as name_path
        from treeNodes
       where id = '1' /* from root */
      union all
      select tn.id,
             tn.name,
             tn.upid,
             tp.id_path || '->' || tn.id as id_path,
             tp.name_path || '->' || tn.name as name_path
        from treeNodes tn
        join tmp tp
          on tp.id = tn.upid
  )
select * from tmp