Select * from …. Where [结果过滤条件语句]
Start with [and起始条件过滤语句]
Connect by prior [and中间记录过滤条件语句]
一、Start with.........Connect By 子句
START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row。
二、示例
1. 创建测试表：
CREATE
TABLE
DG_Test(D_ID
NUMBER
, D_NAME
VARCHAR2
(
100
BYTE), D_PID
NUMBER
, D_Enabled
NUMBER
)
2. 插入测试数据：
INSERT
NTO dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
1
,
'
10
'
,
0
,
1
);
INSERT
INTO
dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
2
,
'
11
'
,
1
,
1
);
INSERT
INTO
dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
3
,
'
20
'
,
0
,
1
);
INSERT
INTO
dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
4
,
'
12
'
,
1
,
1
);
INSERT
INTO
dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
5
,
'
121
'
,
2
,
1
);
INSERT
INTO
dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
6
,
'
1215
'
,
5
,
0
);
INSERT
INTO
dg_test(D_ID,D_NAME,D_PID,D_ENABLED)
VALUES
(
7
,
'
1214
'
,
4
,
0
);
3. select * from DG_Test
4. 上述单表情况一般用于维护树型结构的应用
从Root往树末梢递归
select * from dg_test
start with D_id=1
connect by prior D_id = D_pid
从末梢往树ROOT递归
select * from dg_test
start with D_id=7
connect by D_id = prior D_pid
start with 后面跟着树根，定义了树开始显示的起点位置，如上面两种查询，不管从树根到树顶，还是从树顶到树根。
5. 排除单一体及其分支
select t.* from dg_test t
start with D_id=1
connect by prior D_id = D_pid
and d_id != 2
这个语句表示从D_id为2的开始，包括后面的子分支，全部排除
注意：这里和where条件的过虑方式不一样，where条件只是把相应的某条不符合要求的记录排除。
