sql递归查询解决方法

sql递归查询

SQL code

create table ttt(ID INT,A_ID VARCHAR(1000),Z_ID VARCHAR(1000))insert into ttt select 1,a,b;insert into ttt select 2,c,b;insert into ttt select 3,c,d;insert into ttt select 4,d,e;insert into ttt select 5,f,e;insert into ttt select 6,n,m;insert into ttt select 7,p,n;insert into ttt select 8,p,q;

传入查询条件a,m
期望查询结果：
列1，列2，列3，列4，列5，列6
a b c d e f
m n p q

希望高手帮我解决一下，怎样能查出来？

[解决办法]
with t as (
select 'a' as code,'' as parentcode from dual
union all
select 'b','a' from dual
union all
select 'c','b' from dual
union all 
select 'd','c' from dual
union all
select 'm','' from dual
union all
select 'n','m' from dual
union all
select 'o','n' from dual
) 
select max(allpath) from (
SELECT sys_connect_by_path(parentcode,'-->') || '-->' || code AS allPath,
rownum - level as lvl
FROM t
START WITH (code = 'a' or code = 'm')
CONNECT BY prior code = parentcode
)
group by lvl;

MAX(ALLPATH)
--------------------------
-->-->m-->n-->o
-->-->a-->b-->c-->d
[解决办法]

探讨

SQL code

create table ttt(ID INT,A_ID VARCHAR(1000),Z_ID VARCHAR(1000))
insert into ttt select 1,a,b;
insert into ttt select 2,c,b;
insert into ttt select 3,c,d;
insert into ttt select 4,d,e;
……

查看更多 下一篇