oracle递归查询

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.创建测试表：

CREATETABLEDG_Test
(
D_IDNUMBER,D_NAMEVARCHAR2(100BYTE),D_PIDNUMBER,D_EnabledNUMBER)

2.插入测试数据：

INSERT NTOdg_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条件只是把相应的某条不符合要求的记录排除。