oracle层次查询，挑战高手！！！

Question

本帖最后由 lixianzhang0493 于 2010-01-13 19:26:48 编辑

Answer 1

要是我就用union拉倒
select * from(
select a,b,' ' as c,' ' as d from tbl group by a,b
union all
select a,b, c,' ' as d from tbl group by a,b,c
unoin all
select a,b,c,d from tbl)
order by a,b,c,d

Answer 2

SQL> select * from tb;A  B  C  D                                                                      
-- -- -- --                                                                     
1  2  3  a                                                                      
1  2  3  b                                                                      
1  2  4  c                                                                      
1  2  4  d                                                                      
1  2  4  e                                                                      
1  3  5  o                                                                      
1  3  5  p                                                                      
1  3  5  l                                                                      
2  6  7  a                                                                      
2  6  8  b                                                                      已选择10行。SQL> select distinct a,b,null,null from tb
  2  union all
  3  select distinct a,b,c,null from tb
  4  union all
  5  select distinct a,b,c,d from tb
  6  order by 1,2,3 nulls first,4 nulls first;A  B  NU NU                                                                     
-- -- -- --                                                                     
1  2                                                                            
1  2  3                                                                         
1  2  3  a                                                                      
1  2  3  b                                                                      
1  2  4                                                                         
1  2  4  c                                                                      
1  2  4  d                                                                      
1  2  4  e                                                                      
1  3                                                                            
1  3  5                                                                         
1  3  5  l                                                                      
1  3  5  o                                                                      
1  3  5  p                                                                      
2  6                                                                            
2  6  7                                                                         
2  6  7  a                                                                      
2  6  8                                                                         
2  6  8  b                                                                      已选择18行。

Answer 3

lz思维有问题，通常要是做树的话，只记录父子关系就可以了，然后要求select出全部路径还有道理。

Answer 4

《Oracle Database 11g SQL 开发指南》
第7.5节层次化查询 就讲这些东西，楼主有空看看吧！
比在这里问到的要全面。。

Answer 5

 一般这样的表应该只需要两个字段 父节点和子节点 
难道楼主给的表就是一个已经处理过一次的? 
不知道楼主想要什么样的方法? 
根据你给的 字段A是字段B的父节点 
字段B是字段C的父节点 
字段C是字段D的父节点  这样的规律  三楼的方法我觉得就是最好的了!