Start with...Connect By子句递归查询
下面是从网上转载的内容:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
CREATE
TABLE
TBL_TEST
(
ID NUMBER,
NAME
VARCHAR2(100 BYTE),
PID NUMBER
DEFAULT
0
);
#插入测试数据:
INSERT
INTO
TBL_TEST(ID,
NAME
,PID)
VALUES
(
''
''
1
''
''
,
''
''
10
''
''
,
''
''
0
''
''
);
INSERT
INTO
TBL_TEST(ID,
NAME
,PID)
VALUES
(
''
''
2
''
''
,
''
''
11
''
''
,
''
''
1
''
''
);
INSERT
INTO
TBL_TEST(ID,
NAME
,PID)
VALUES
(
''
''
3
''
''
,
''
''
20
''
''
,
''
''
0
''
''
);
INSERT
INTO
TBL_TEST(ID,
NAME
,PID)
VALUES
(
''
''
4
''
''
,
''
''
12
''
''
,
''
''
1
''
''
);
INSERT
INTO
TBL_TEST(ID,
NAME
,PID)
VALUES
(
''
''
5
''
''
,
''
''
121
''
''
,
''
''
2
''
''
);
#从Root往树末梢递归
select
*
from
TBL_TEST
start
with
id=1
connect
by
prior
id = pid
#从末梢往树ROOT递归
select
*
from
TBL_TEST
start
with
id=5
connect
by
prior
pid = id
|