sql递归查询

Question

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希望高手帮我解决一下，怎样能查出来？

Answer 1

是希望得到这个结果吗？
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
union all
select 'p','o' from dual
) 
select wm_concat(code) as a from t
start with code = 'a'
connect by prior code = parentcode
union
select wm_concat(code) from t
start with (code = 'm')
connect by prior code = parentcode--result
A                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
--------
a,b,c,d 
m,n,o,p 

Answer 2

你好，a，b，c分别在三列，传入a时查出：a   b   c   d   e   f  六列
传入m时查出m  n  p  q  四列，你看能实现这个吗？

Answer 3

创建sql：
create table ttt(ID INT,A_ID VARCHAR(1000),Z_ID VARCHAR(1000));
insert into ttt(id,a_id,z_id) values (1,'a','b');
insert into ttt (id,a_id,z_id) values (2,'c','b');
insert into ttt (id,a_id,z_id) values (3,'c','d');
insert into ttt (id,a_id,z_id) values (4,'d','e');
insert into ttt (id,a_id,z_id) values (5,'f','e');insert into ttt (id,a_id,z_id) values (6,'n','m');
insert into ttt (id,a_id,z_id) values (7,'p','n');
insert into ttt (id,a_id,z_id) values (8,'p','q');

Answer 4

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   

Answer 5

你的数据不对啊，我的数据里面不分父子关系，只有A,Z两端，如：
insert into ttt(id,a_id,z_id) values (1,'a','b');
insert into ttt (id,a_id,z_id) values (2,'c','b');
用CONNECT BY prior code = parentcode就不能把两条数据天联起来。这个方法应该不可以

Answer 6

你好，a，b，c分别在三列，传入a时查出：a b c d e f 六列
传入m时查出m n p q 四列，你看能实现这个吗？规律是啥，没看出来

Answer 7

数据不对create table ttt(ID INT,A_ID VARCHAR(1000),Z_ID VARCHAR(1000))
insert into ttt select 1,b,a;
insert into ttt select 2,c,b;
insert into ttt select 3,d,c;
insert into ttt select 4,e,d;
insert into ttt select 5,f,e; 传入查询条件a,m
期望查询结果：
列1，列2，列3，列4，列5，列6
a b c d e f
m n p q
-------------------------------------
向上面那样插入数据才可以的使用start with ...    connect by ..
语句

Answer 8

数据只是A-Z两端的对应关系，没有父子关系，所以用connect by with应该查不出来。