求助查询树状结构数据.

Question

我有一个表，是树状结构 
parent  child  child_type 
------------------------ 
p1        c1      normal 
p1        c2      special
c2        s1      normal
s1        n1      normal 
c2        s2      special
s2        D1      normal
s2        D2      special
D2        E1      normal
D2        E2      special
 .         .        .
 .         .        .
 .         .        .
c2        s3      normal 
 结构如下 
p1 
  ┣ c1 
  ┗ c2 
      ┣ s1 
      ┃  ┗ n1 
      ┃
      ┣ s2    
      ┃ ┣ D1 
      ┃ ┗ D2 
      ┃     ┣ E1 
      ┃     ┗ E2 
      ┃        ┣ ..... 
      ┃        ┗ .....
      ┗ s3我想要下面的查询结果 
parent  child  child_type 
----------------------------- 
p1        c1      normal
C2        S1      normal
S2        D1      normal
D2        E1      normal
 .         .      normal
 .         .      normal
 .         .      normal
C2        S3      normal
说明：把子类型为normal的数据全部显示出来,如果子类型为special,继续向树的树叉查询,直至查询到子类型为normal的数据.
需要注意的是normal类型的数据的所有下层都不显示，即遇到normal类型的数据该树的这个分支就截止，如n1 ,也就是假如C1,C2为normal的数据,则只显示树根P1.这个查询好难的，哪位专家有办法？

Answer 1

select *
from table1
where connect_by_isleaf=1
start with parent='p1'
connect by prior child=parent
  and prior child_type='special'

Answer 2

CONNECT_BY_ISLEAF=1为什么不oracle9.2不认识.

Answer 3

这个好像10g以上版本才支持
换成这样试试
select max(parent)keep(dense_rank last order by level)parent,
  max(child)keep(dense_rank last order by level)child,
  max(child_type)keep(dense_rank last order by level)child_type
from table1
start with parent='p1'
connect by prior child=parent
  and prior child_type='special'
group by rownum-level;

Answer 4

兄弟,我试了,这个语句有问题的具体的数据如下:
PARENT                                             CHILD                                              CHILD_TYPE
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
p1                                                 c1                                                 normal
c2                                                 s1                                                 normal
s2                                                 D1                                                 normal
D2                                                 E1                                                 normal
D2                                                 E2                                                 special
c2                                                 s3                                                 normal已选择6行。

Answer 5

PARENT       CHILD         CHILD_TYPEp1           c1              normal
c2           s1              normal
s2           D1              normal
D2           E1              normal
D2           E2              pecial
c2           s3              normal已选择6行。

Answer 6

这是由于
D2          E2              pecial 
这条记录导致的
因为在你的表中E2再没有下级子节点了 因此这条记录就满足了条件connect_by_isleaf=1,也就被显式出来了解决办法么在外层再加个select过滤一下就可以了
SQL> select * from t1;P          C          TYPE
---------- ---------- ----------
p1         c1         n
p1         c2         s
c2         s1         n
s1         n1         n
c2         s2         s
s2         d1         n
s2         d2         s
d2         e1         n
d2         e2         s9 rows selected.
SQL> select *
  2  from t1
  3  where connect_by_isleaf=1
  4  start with p='p1'
  5  connect by prior c=p
  6  and prior type='s';P          C          TYPE
---------- ---------- ----------
p1         c1         n
c2         s1         n
s2         d1         n
d2         e1         n
e2         f1         n
SQL> select * from
  2  (
  3  select *
  4  from t1
  5  where connect_by_isleaf=1
  6  start with p='p1'
  7  connect by prior c=p
  8  and prior type='s'
  9  )
 10  where type='n';P          C          TYPE
---------- ---------- ----------
p1         c1         n
c2         s1         n
s2         d1         n
d2         e1         n

Answer 7

上面倒数第二个查询有些不正确 那是我加了一条测试数据以后的显示结果
原结果应该如下所示
SQL> select *
  2  from t1
  3  where connect_by_isleaf=1
  4  start with p='p1'
  5  connect by prior c=p
  6  and prior type='s';P          C          TYPE
---------- ---------- ----------
p1         c1         n
c2         s1         n
s2         d1         n
d2         e1         n
d2         e2         s  --这就是多出来的那条数据

Answer 8

狂浪的那句修改下：
select * from
(select max(parent)keep(dense_rank last order by level)parent,
  max(child)keep(dense_rank last order by level)child,
  max(child_type)keep(dense_rank last order by level)child_type
from table1
start with parent='p1'
connect by prior child=parent
  and prior child_type='special'
group by rownum-level)
where child_type<>'special';

Answer 9

从树根到截止结点,所有经过的结点都相乘,存放在COUNT字段中,怎么处理
PARENT       CHILD         CHILD_TYPE   COUNTp1           c1              normal     P1*C1
c2           s1              normal     P1*C2*S1
s2           D1              normal     P1*C2*S2*D1
D2           E1              normal     P1*C2*S2*D2*E1
c2           s3              normal     p1*C2*S3