oracle的层次查询之 connect_by_root，sys_connect_by_path

项目中的需求是：点击一个机构，查询下级机构，并汇总下级机构的业务数据，形成如下界面。

例如：

==机构==      ==业务量==

+BB银行总行      1000

--华北区              200

   ---部门1           200

       ---客户经理1  100

       ---客户经理2  100

--华南区               600

  ---部门2            

       ---客户经理3  

       ---客户经理4

 

 

 

--区域1

       ---客户经理5

--区域2

 

各个机构下有客户经理，我们只有客户经理的业务数据，所有的机构都是通过客户经理来进行汇总业务量的。我们可以递归查到BB银行总行的所有的客户经理，这些客户经理需要分组汇总到各个子节点。

 

问题的难点  就在于如何得到客户经理究竟属于 哪个机构 是“华北区”还是“华南区”。

 

采用oracle的connect_by_root ，sys_connect_by_path(hier.child,'/')函数来找到这个机构的顶级root节点，那么我们就可以按照顶级节点进行汇总数据了。

以下sql是查询出**银行下的所有分行以及其属于哪个分行。

select level,connect_by_root parent,hier.child,parent,sys_connect_by_path(hier.child,'/')path 
from hier 
start with parent in
 (select child from hier where parent is null)
) 
connect by prior hier.child = parent; 

 

 附件：数据为网上转帖http://www.builder.com.cn/2005/0809/206241.shtml

，大致sql相同

 

 

 

 

create table hier
(
parent varchar2(30),
child varchar2(30)
);

insert into hier values(null,'Asia');
insert into hier values(null,'Australia');
insert into hier values(null,'Europe');
insert into hier values(null,'North America');
insert into hier values('Asia','China');
insert into hier values('Asia','Japan');
insert into hier values('Australia','New South Wales');
insert into hier values('New South Wales','Sydney');
insert into hier values('California','Redwood Shores');
insert into hier values('Canada','Ontario');
insert into hier values('China','Beijing');
insert into hier values('England','London');
insert into hier values('Europe','United Kingdom');
insert into hier values('Japan','Osaka');
insert into hier values('Japan','Tokyo');
insert into hier values('North America','Canada');
insert into hier values('North America','USA');
insert into hier values('Ontario','Ottawa');
insert into hier values('Ontario','Toronto');
insert into hier values('USA','California');
insert into hier values('United Kingdom','England'); 

 

 

 

 下面是部分查询结果：

 

1     Asia    China Asia /China
2    Asia    Beijing China /China/Beijing
1    Asia     Japan Asia /Japan
2    Asia    Osaka Japan /Japan/Osaka
2    Asia       Tokyo Japan /Japan/Tokyo
1    Australia    New South Wales Australia /New South Wales