oracle层级表 要求列出子节点

Question

我的表mytable中这2个字段  一个子节点 一个父节点  organization_id_child    organization_id_parent
如果我有这样的数据   
organization_id_child    organization_id_parent
A                               0
B                               A
C                               B
D                               B
E                               C
F                               D  
我想得到A  B   C  E 
        A  B   D  F 
这样的数据  直接写SQL能实现么？

Answer 1

http://space.itpub.net/17309626/viewspace-663124

Answer 2

SQL> with temp as(
  2      select 'A' as organization_id_child ,'o' as organization_id_parent from dual
  3      union all
  4      select 'B' as organization_id_child ,'A' as organization_id_parent from dual
  5      union all
  6      select 'C' as organization_id_child ,'B' as organization_id_parent from dual
  7      union all
  8      select 'D' as organization_id_child ,'B' as organization_id_parent from dual
  9      union all
 10      select 'E' as organization_id_child ,'C' as organization_id_parent from dual
 11      union all
 12      select 'F' as organization_id_child ,'D' as organization_id_parent from dual
 13      )
 14      select replace(sys_connect_by_path(organization_id_child,'*'),'*','') from temp
 15      where connect_by_isleaf = 1
 16      start with organization_id_parent='o'
 17      connect by prior organization_id_child = organization_id_parent
 18      ;REPLACE(SYS_CONNECT_BY_PATH(OR
--------------------------------------------------------------------------------
ABCE
ABDFSQL> 

Answer 3

--如果9i，不支持connect_by_isleaf，这样
SELECT REPLACE(sys_connect_by_path(organization_id_child, ','), ',', '')
  FROM (SELECT a.*,
               (SELECT COUNT(*)
                  FROM temp t
                 START WITH t.organization_id_parent =
                            a.organization_id_child
                CONNECT BY PRIOR
                            t.organization_id_child = t.organization_id_parent) is_leaf
          FROM temp a)
 WHERE is_leaf = 0
 START WITH organization_id_parent = 'o'
CONNECT BY PRIOR organization_id_child = organization_id_parent;

Answer 4

嗯  对的 10g新增connect_by_isleaf
我的环境是10g的
所以。。呵呵