首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > oracle >

sql递归查询解决方法

2012-05-23 
sql递归查询SQL codecreate table ttt(ID INT,A_ID VARCHAR(1000),Z_ID VARCHAR(1000))insert into ttt se

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;
……

热点排行