# 对"一个非常难的查询问题(部门上下级的关系)"之解答的完善

2 篇文章 0 订阅

http://community.csdn.net/Expert/FAQ/FAQ_Index.asp?id=170559

--------------------------------------------------------------------------------

id    name
1        a
2        b
3        c
4        d
5        e

id1    id2
1        2
2        3
2        4
3        5

id    full_name
1        a
2        a/b
3        a/b/c
4        a/b/d
5        a/b/c/d

---------------------------------------------------------

LEVEL
----------
1
2
3
4
3

SQL> select level from table_B connect by prior id2=id1 start with id1=1;

LEVEL
----------
1
2
3
2

SQL> select level from table_B connect by prior id2=id1 start with id1=2;

LEVEL
----------
1
2
1

SQL> select level from table_B connect by prior id2=id1 start with id1=3;

LEVEL
----------
1

SQL> select level from table_B connect by prior id2=id1 start with id1=4;

LEVEL
----------

SQL> select level from table_B connect by prior id2=id1 start with id1=5;

LEVEL
----------

select lpad(id2, level*length(id2), ' ') id,
2     ltrim(sys_connect_by_path(id2,'/'), '/') path
3  from Table_B
4  connect by prior id2=id1
6  /

ID                                                                               PATH
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1                                                                                1
2                                                                               1/2
3                                                                              1/2/3
5                                                                             1/2/3/5
4                                                                              1/2/4

DROP TABLE Table_A;

Table dropped

SQL> create table Table_A (id number(4), name varchar2(20));

Table created

SQL> insert into Table_A values(1, 'a');

1 row inserted

SQL> insert into Table_A values(2, 'b');

1 row inserted

SQL> insert into Table_A values(3, 'c');

1 row inserted

SQL> insert into Table_A values(4, 'd');

1 row inserted

SQL> insert into Table_A values(5, 'e');

1 row inserted

SQL> commit;

Commit complete

SQL> DROP TABLE Table_B;

Table dropped

SQL> create table Table_B (id1 number(4), id2 number(4));

Table created

SQL> insert into table_B values(0,1);

1 row inserted

SQL> insert into Table_B values(1,2);

1 row inserted

SQL> insert into Table_B values(2,3);

1 row inserted

SQL> insert into Table_B values(2,4);

1 row inserted

SQL> insert into Table_B values(3,5);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT id2, ltrim(sys_connect_by_path(NAME, '/'), '/') path
2  from
3   (SELECT B.*, A.NAME
4   FROM Table_B B, Table_A A
5   WHERE B.id2=A.id)
6  connect by prior id2=id1
8  ORDER BY id2
9  /

ID2 PATH
----- --------------------------------------------------------------------------------
1 a
2 a/b
3 a/b/c
4 a/b/d
5 a/b/c/e

• 0
点赞
• 0
收藏
• 打赏
• 5
评论
04-27 253
03-25
05-01 471
02-27 742
04-16 477
03-15 1618
01-10 1138
07-24 2908
08-17 638
06-11 3万+
09-17 2203
06-20 5694
11-25 5万+

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

lbl20020123

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。