?

转至：http://erplife.blog.sohu.com/84644463.html

摘要：本文将根据对层次查询语句简单例子的说明来理解应用，并举例实际的应用案例。
========================================================================================================
start with connect by 层次查询（Hierarchical Queries）
========================================================================================================
语法：
--------------------------------------------------------------------
SELECT *
? FROM table
?WHERE?
?START WITH?
?CONNECT BY?
?ORDER BY col1, col2 ...

SELECT???? *
????? FROM table
START WITH ID = 1
CONNECT BY PRIOR PID = ID

start with: 表示根记录的条件
connect by: 指定了父记录行和子记录行之间的关系，在层次查询中，条件表达式必须使用prior操作符来指定父记录行
如：
CONNECT BY PRIOR pid = id 或者CONNECT BY pid = PRIOR id
如果connect by 条件是一个组合条件，那么只有一个条件需要prior操作符，
如：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id

不过，connect by 不能包含子查询。
prior是一个二元操作符，最常见的是用于列值相等的比较，它让Oracle使用对应列的父亲行的值。使用非相等比较，极有可能倒致查询陷入无穷循环，以出错终止。

举例：
========================================================================================================
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。可以通过一个简单的例子来理解其使用的概念和方法。
创建示例表：
--------------------------------------------------------------------
CREATE TABLE tbl_test
(
? ID??? NUMBER,
? NAME? VARCHAR2(10),
? pid?? NUMBER DEFAULT 0
);
?
插入测试数据：
--------------------------------------------------------------------
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','111','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','222','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','333','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','444','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','555','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('6','666','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('9','999','2');
?
全部记录
--------------------------------------------------------------------
SELECT * FROM tbl_test
如下记录
--------------------
ID?NAME?PID
1?111?0?
2?222?1?
3?333?0?
4?444?1?
5?555?2?
6?666?0?
9?999?2?

从父记录行向子记录行递归
--------------------------------------------------------------------
SELECT???? *
????? FROM tbl_test
START WITH ID = 1
CONNECT BY PRIOR ID = pid
如下记录
--------------------
ID?NAME?PID
1?111?0?
2?222?1?
5?555?2?
9?999?2?
4?444?1?
--------------------
解析：
1.(START WITH ID = 1)根记录条件为ID＝1
2.(CONNECT BY PRIOR ID = pid):由列ID与PID建立父子关系并进行比较，从ID为1开始，在PID列中寻找为1的行，可以找到ID为2和4，再将ID为2和4从PID中再寻找，又可以找到5和9,以上结果因此而来。
?
从子记录向父记录递归
--------------------------------------------------------------------
SELECT???? *
????? FROM tbl_test
START WITH ID = 5
CONNECT BY PRIOR pid = ID
如下记录
--------------------
ID?NAME?PID
5?555?2?
2?222?1?
1?111?0?
--------------------
解析：
1.(START WITH ID = 5)根记录条件为ID＝5
2.(CONNECT BY PRIOR pid = ID)：以之上查询恰相反，其中的取值也正相反。ID为5的PID列的值为2，因PRIOR在PID列一边，确从PID列中取值，在PID列取值2向ID列进行递归查询，在ID列中找到2的值，再确认其对应的PID为1，再次取值1在ID中找到结果，最终共计三条记录。

========================================================================================================
LEVEL,ROW_NUMBER,OVER的应用
========================================================================================================
设PID为父值，并根据PID进行分组及确定LEVEL
--------------------------------------------------------------------
SELECT???? LEVEL, pid,
?????????? ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid) by_pid,
?????????? ROW_NUMBER () OVER (ORDER BY pid) AS rn, NAME
????? FROM tbl_test
START WITH pid = 0
CONNECT BY PRIOR ID = pid
? ORDER BY 1
如下记录