oracle connect by用法
create table t_location(
id number primary key ,
name varchar (16 ) not null ,
pid number not null
)
insert into t_location values (1 ,'华东区' ,0 );
insert into t_location values (2 ,'华南区' ,0 );
insert into t_location values (3 ,'福建省' ,1 );
insert into t_location values (4 ,'江苏省' ,1 );
insert into t_location values (5 ,'福州市' ,3 );
insert into t_location values (6 ,'厦门市' ,3 );
insert into t_location values (7 ,'苏州市' ,4 );
insert into t_location values (8 ,'扬州市' ,4 );
insert into t_location values (9 ,'广东省' ,2 );
insert into t_location values (10 ,'广西省' ,2 );
insert into t_location values (11 ,'广州市' ,9 );
insert into t_location values (12 ,'深圳市' ,9 );
insert into t_location values (13 ,'南宁市' ,10 );
insert into t_location values (14 ,'桂林市' ,10 );
select t.*, level , CONNECT_BY_ROOT(id)
from t_location t
start with t.id = 1
connect by prior t.id = t.pid;
select t.*, level , CONNECT_BY_ROOT(id)
from t_location t
start with t.id = 14
connect by t.id = prior t.pid;
select t.*, level , CONNECT_BY_ROOT(id)
from t_location t
start with t.id = 14
connect by prior t.pid = t.id;
select replace (wm_concat(name),',' ,' ' ) as addr from (
select t.name,connect_by_root(id) as rid
from t_location t
start with t.name = '深圳市'
connect by prior t.pid = t.id
order by level desc ;
)
select rid,replace (wm_concat(name),',' ,' ' ) as addr from (
select t.name,connect_by_root(id) as rid
from t_location t
connect by prior t.pid = t.id
order by level desc
)
group by rid;