少些了个by,还不能编辑 select * from table connect by prior id=parentid start with parentid is null order sublings by id
晕,单词还拼错一个,洗洗睡了 select * from table connect by prior id=parentid start with parentid is null order siblings by id
with t as (select 1 id, 'shizhengfu' nm, null pid from dual union all select 2 id, 'gonganju' nm, 1 pid from dual union all select 3 id, 'shuiliju' nm, 1 pid from dual union all select 4 id, 'gonganjuxiashu1' nm, 2 pid from dual union all select 5 id, 'gonganjuxiashu2' nm, 2 pid from dual union all select 6 id, 'shuilijuxiashu1' nm, 3 pid from dual union all select 7 id, 'shuilijuxiashu2' nm, 3 pid from dual) select ltrim(sys_connect_by_path(nm, ','), ','), level from t connect by prior id = pid;
利用sys_connect_by_path获取树节点路径就可以了 select sys_connect_by_path(name, ',') AS PATH,A.* from table A connect by prior id=parentid start with parentid is null order siblings by id
是不是要这样的结果,两两配对的上下属关系?表自连接可实现 select A.NAME,B.NAME from table A,table B WHERE B.id in (SELECT ID FROM TABLE connect by prior id=parentid start with parentid=A.ID)
刚才说错了,要得到的字符串都是从当前部门名称 到 最高部门的,所以不存在低级部门组成的字符串 比如当前部门是shuilijuxiashu1 求出的结果: shuilijuxiashu1,shuiliju,shizhengfuwith t as (select 1 id, 'shizhengfu' nm, null pid from dual union all select 2 id, 'gonganju' nm, 1 pid from dual union all select 3 id, 'shuiliju' nm, 1 pid from dual union all select 4 id, 'gonganjuxiashu1' nm, 2 pid from dual union all select 5 id, 'gonganjuxiashu2' nm, 2 pid from dual union all select 6 id, 'shuilijuxiashu1' nm, 3 pid from dual union all select 7 id, 'shuilijuxiashu2' nm, 3 pid from dual) select max(ltrim(sys_connect_by_path(nm, ','), ',')) from t connect by id = prior pid start with nm = 'shuilijuxiashu1';
select id, name from test where parentid is null union all select t1.parentid,concat(t1.name, t2.name) name from test t1, test t2 where t1.id=t2.parentid union allselect t1.parentid,concat(t1.name,t2.name) from test t1, (select t1.parentid,concat(t1.name, t2.name) name from test t1, test t2 where t1.id=t2.parentid) t2 where t1.id=t2.parentid and t2.parentid is not nullunion allselect t1.parentid,concat(t1.name,t3.name) from test t1, (select t1.parentid,concat(t1.name,t2.name) name from test t1, (select t1.parentid,concat(t1.name, t2.name) name from test t1, test t2 where t1.id=t2.parentid) t2 where t1.id=t2.parentid and t2.parentid is not null) t3 where t1.id=t3.parentid and t3.parentid is not null;这个你可以用循环,在存储过程来把上面的sql语句简化;
看看是这样的结果不? select a.name||sys_connect_by_path(b.name,',') from table a,table b connect by prior b.id=b.parentid start with b.parentid=a.id order siblings by a.id,b.id
上面少写了个连接条件 select a.name||sys_connect_by_path(b.name,',') from table a,table b connect by prior b.id=b.parentid and prior a.id=a.id start with b.parentid=a.id order siblings by a.id,b.id
select * from table
connect by prior id=parentid
start with parentid is null
order sublings by id
select * from table
connect by prior id=parentid
start with parentid is null
order siblings by id
with t as
(select 1 id, 'shizhengfu' nm, null pid
from dual
union all
select 2 id, 'gonganju' nm, 1 pid
from dual
union all
select 3 id, 'shuiliju' nm, 1 pid
from dual
union all
select 4 id, 'gonganjuxiashu1' nm, 2 pid
from dual
union all
select 5 id, 'gonganjuxiashu2' nm, 2 pid
from dual
union all
select 6 id, 'shuilijuxiashu1' nm, 3 pid
from dual
union all
select 7 id, 'shuilijuxiashu2' nm, 3 pid
from dual)
select ltrim(sys_connect_by_path(nm, ','), ','), level
from t
connect by prior id = pid;
select sys_connect_by_path(name, ',') AS PATH,A.* from table A
connect by prior id=parentid
start with parentid is null
order siblings by id
select A.NAME,B.NAME
from table A,table B
WHERE B.id in
(SELECT ID FROM TABLE connect by prior id=parentid start with parentid=A.ID)
刚才说错了,要得到的字符串都是从当前部门名称 到 最高部门的,所以不存在低级部门组成的字符串
比如当前部门是shuilijuxiashu1
求出的结果:
shuilijuxiashu1,shuiliju,shizhengfuwith t as
(select 1 id, 'shizhengfu' nm, null pid
from dual
union all
select 2 id, 'gonganju' nm, 1 pid
from dual
union all
select 3 id, 'shuiliju' nm, 1 pid
from dual
union all
select 4 id, 'gonganjuxiashu1' nm, 2 pid
from dual
union all
select 5 id, 'gonganjuxiashu2' nm, 2 pid
from dual
union all
select 6 id, 'shuilijuxiashu1' nm, 3 pid
from dual
union all
select 7 id, 'shuilijuxiashu2' nm, 3 pid
from dual)
select max(ltrim(sys_connect_by_path(nm, ','), ','))
from t
connect by id = prior pid
start with nm = 'shuilijuxiashu1';
union all
select
t1.parentid,concat(t1.name, t2.name) name
from test t1, test t2
where t1.id=t2.parentid
union allselect t1.parentid,concat(t1.name,t2.name)
from test t1,
(select
t1.parentid,concat(t1.name, t2.name) name
from test t1, test t2
where t1.id=t2.parentid) t2
where t1.id=t2.parentid and t2.parentid is not nullunion allselect t1.parentid,concat(t1.name,t3.name)
from test t1,
(select t1.parentid,concat(t1.name,t2.name) name
from test t1,
(select
t1.parentid,concat(t1.name, t2.name) name
from test t1, test t2
where t1.id=t2.parentid) t2
where t1.id=t2.parentid and t2.parentid is not null) t3
where t1.id=t3.parentid and t3.parentid is not null;这个你可以用循环,在存储过程来把上面的sql语句简化;
select a.name||sys_connect_by_path(b.name,',') from table a,table b
connect by prior b.id=b.parentid
start with b.parentid=a.id
order siblings by a.id,b.id
select a.name||sys_connect_by_path(b.name,',') from table a,table b
connect by prior b.id=b.parentid and prior a.id=a.id
start with b.parentid=a.id
order siblings by a.id,b.id