Oracle层次查询及应用(start with connect by)

 

转至：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
如下记录
--------------------------------------------------------------------------------------------------------
LEVEL PID BY_PID RN NAME
1 0 1 1 111 
1 0 2 2 333 
1 0 3 3 666 
2 1 1 4 222 
2 1 2 5 444 
3 2 1 6 555 
3 2 2 7 999 

根据父值逐层区分
--------------------------------------------------------------------------------------------------------
SELECT     pid, ID,
           (CASE
               WHEN LEVEL = 1
                  THEN NAME
               WHEN LEVEL = 2
                  THEN '             ' || NAME
               WHEN LEVEL = 3
                  THEN '                           ' || NAME
            END
           ) bs_name
      FROM tbl_test
START WITH pid = 0
CONNECT BY pid = PRIOR ID

记录如下：
--------------------------------------------------------------------------------------------------------
PID ID BS_NAME
0 1 111 
1 2              222 
2 5                            555 
2 9                            999 
1 4              444 
0 3 333 
0 6 666 

可以清楚看出，ID为1，其子值为2和4(level 2)，而2值又有子值5、9(level 3)，3、6无子值存在。

========================================================================================================
SYS_CONNECT_BY_PATH 函数
========================================================================================================
以上例显示看出，PID分为三个分支，NAME分别如下：
第一分支：111，333，666
第二分支：222，444
第三分支：555，999

脚本：
--------------------------------------------------------------------------------------------------------
SELECT     pid, SYS_CONNECT_BY_PATH (by_path, ',')
      FROM (SELECT   pid,
                     ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid)
                                                                       by_pid,
                     ROW_NUMBER () OVER (ORDER BY pid) + pid AS rn,
                     NAME AS by_path
                FROM tbl_test
            ORDER BY 1)
START WITH by_pid = 1
CONNECT BY rn - 1 = PRIOR rn
  ORDER BY 1

记录结果：
--------------------------------------------------------------------------------------------------------
PID SYS_CONNECT_BY_PATH(BY_PATH,',')
0 ,111 
0 ,111,333 
0 ,111,333,666 
1 ,222 
1 ,222,444 
2 ,555 
2 ,555,999 

如取单值列，可取其中最大值，使用MAX，然后应用GROUP BY即可，如下脚本：
--------------------------------------------------------------------------------------------------------
SELECT     pid, LTRIM (MAX (SYS_CONNECT_BY_PATH (by_path, ',')), ',')
      FROM (SELECT   pid,
                     ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid)
                                                                       by_pid,
                     ROW_NUMBER () OVER (ORDER BY pid) + pid AS rn,
                     NAME AS by_path
                FROM tbl_test
            ORDER BY 1)
START WITH by_pid = 1
CONNECT BY rn - 1 = PRIOR rn
  GROUP BY pid
  ORDER BY 1

记录结果：
--------------------------------------------------------------------------------------------------------
PID LTRIM(MAX(SYS_CONNECT_BY_PATH(BY_PATH,',')),',')
0 111,333,666 
1 222,444 
2 555,999 
--------------------------------------------------------------------------------------------------------
常用于行列转换的应用。

应用：
一、ERP BOM（物料清单）
========================================================================================================
SELECT DISTINCT b.lvl lv, msi1.segment1 p_item, msi1.description p_item_desc,
                msi1.primary_uom_code, b.item_num num, b.operation_seq_num,
                msi2.segment1 c_item, msi2.description c_item_desc,
                msi2.primary_uom_code, b.component_quantity,
                b.component_yield_factor,
                DECODE (b.wip_supply_type,
                        1, 'Push',
                        2, 'Assembly Pull'
                       ) TYPE, b.supply_subinventory, b.planning_factor
           FROM inv.mtl_system_items_b msi1,
                inv.mtl_system_items_b msi2,
                bom.bom_structures_b bom,
                inv.mtl_parameters mp,
                (SELECT     LEVEL lvl, bic.bill_sequence_id,
                            bic.component_item_id, bic.component_quantity,
                            bic.component_yield_factor, bic.operation_seq_num,
                            bic.item_num, bic.wip_supply_type,
                            bic.supply_subinventory, bic.effectivity_date,
                            bic.planning_factor
                       FROM bom.bom_components_b bic
                      WHERE disable_date IS NULL AND bic.planning_factor > 0
                 START WITH bic.bill_sequence_id IN (
                               SELECT bill_sequence_id
                                 FROM bom.bom_structures_b bom2,
                                      inv.mtl_system_items_b msi,
                                      inv.mtl_parameters mp
                                WHERE bom2.assembly_item_id = msi.inventory_item_id
                                  AND bom2.organization_id = msi.organization_id
                                  AND msi.segment1 = 'FQH1AU3ACBBH34HD02'
                                  AND mp.organization_code = 'ZP1'
                                  AND msi.organization_id = mp.organization_id
                                  AND bom2.alternate_bom_designator IS NULL)
                 CONNECT BY bic.bill_sequence_id =
                               PRIOR (SELECT DISTINCT bill_sequence_id
                                                 FROM bom.bom_structures_b bo,
                                                      inv.mtl_system_items_b msi,
                                                      inv.mtl_parameters mp
                                                WHERE bo.assembly_item_id = bic.component_item_id
                                                  AND mp.organization_code ='ZP1'
                                                  AND bo.organization_id = mp.organization_id
                                                  AND bo.organization_id = msi.organization_id
                                                  AND bo.assembly_item_id =msi.inventory_item_id
                                                  AND bo.alternate_bom_designator IS NULL
                                                  AND disable_date IS NULL)) b
          WHERE b.bill_sequence_id = bom.bill_sequence_id
            AND mp.organization_code = 'ZP1'
            AND bom.organization_id = mp.organization_id
            AND bom.organization_id = msi1.organization_id
            AND bom.assembly_item_id = msi1.inventory_item_id
            AND bom.organization_id = msi2.organization_id
            AND b.component_item_id = msi2.inventory_item_id

二、行列转换
========================================================================================================
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;

DEPTNO ENAME
--------------------------------------------------------------------------------------------------------
    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD

14 rows selected.
想输出为：
DEPTNO ENAME
--------------------------------------------------------------------------------------------------------
    10 CLARK, KING, MILLER
    20 ADAMS, FORD, JONES, SCOTT, SMITH
    30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

除了使用聚集函数或者存储过程之外(行列转换 http://erplife.blog.sohu.com/72186257.html)，9i中可以：
--------------------------------------------------------------------------------------------------------
SELECT     deptno,
           LTRIM
              (MAX (SYS_CONNECT_BY_PATH (ename, ','))KEEP (DENSE_RANK LAST ORDER BY curr),
               ','
              ) AS concatenated
      FROM (SELECT deptno, ename,
                   ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename)
                                                                      AS curr,
                     ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename)
                   - 1 AS prev
              FROM emp)
  GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

记录如下：
--------------------------------------------------------------------------------------------------------
DEPTNO CONCATENATED
10 CLARK,KING,MILLER 
20 ADAMS,FORD,JONES,SCOTT,SMITH 
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 

三、Oracle 10g伪列函数
========================================================================================================
 我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的 层次查询，而Oracle 10g 为其添加许多了新的伪列。

create table hier
 (parent varchar2(30),
  child varchar2(30)
);

insert into hier values(null,'Asia');
insert into hier values(null,'Australia');
insert into hier values(null,'Europe');
insert into hier values(null,'North America');
insert into hier values('Asia','China');
insert into hier values('Asia','Japan');
insert into hier values('Australia','New South Wales');
insert into hier values('New South Wales','Sydney');
insert into hier values('California','Redwood Shores');
insert into hier values('Canada','Ontario');
insert into hier values('China','Beijing');
insert into hier values('England','London');
insert into hier values('Europe','United Kingdom');
insert into hier values('Japan','Osaka');
insert into hier values('Japan','Tokyo');
insert into hier values('North America','Canada');
insert into hier values('North America','USA');
insert into hier values('Ontario','Ottawa');
insert into hier values('Ontario','Toronto');
insert into hier values('USA','California');
insert into hier values('United Kingdom','England');

那么我们可以使用START WITH . . . CONNECT BY . . .从句将父级地区与孩子地区连接起来，并将其层次等级显示出来。

column child format a40
select level,lpad(' ',level*3)||child child
from hier
start with parent is null
connect by prior child = parent;

LEVEL CHILD
---------- --------------------------
1 Asia
2 China
3 Beijing
2 Japan
3 Osaka
3 Tokyo
1 Australia
2 New South Wales
3 Sydney
1 Europe
2 United Kingdom
3 England
4 London
1 North America
2 Canada
3 Ontario
4 Ottawa
4 Toronto
2 USA
3 California
4 Redwood Shores

自从Since Oracle 9i 开始，就可以通过SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示：

column path format a50
select level,sys_connect_by_path(child,'/') path
from hier
start with parent is null
connect by prior child = parent;

LEVEL PATH
-------- --------------------------------------------
1 /Asia
2 /Asia/China
3 /Asia/China/Beijing
2 /Asia/Japan
3 /Asia/Japan/Osaka
3 /Asia/Japan/Tokyo
1 /Australia
2 /Australia/New South Wales
3 /Australia/New South Wales/Sydney
1 /Europe
2 /Europe/United Kingdom
3 /Europe/United Kingdom/England
4 /Europe/United Kingdom/England/London
1 /North America
2 /North America/Canada
3 /North America/Canada/Ontario
4 /North America/Canada/Ontario/Ottawa
4 /North America/Canada/Ontario/Toronto
2 /North America/USA
3 /North America/USA/California
4 /North America/USA/California/Redwood Shores
 
在 Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子：

select connect_by_isleaf,sys_connect_by_path(child,'/') path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ISLEAF PATH
----------------------------------
0 /Asia
0 /Asia/China
1 /Asia/China/Beijing
0 /Asia/Japan
1 /Asia/Japan/Osaka
1 /Asia/Japan/Tokyo
0 /Australia
0 /Australia/New South Wales
1 /Australia/New South Wales/Sydney
0 /Europe
0 /Europe/United Kingdom
0 /Europe/United Kingdom/England
1 /Europe/United Kingdom/England/London
0 /North America
0 /North America/Canada
0 /North America/Canada/Ontario
1 /North America/Canada/Ontario/Ottawa
1 /North America/Canada/Ontario/Toronto
0 /North America/USA
0 /North America/USA/California
1 /North America/USA/California/Redwood Shores
 
在Oracle 10g 中还有一个新操作――CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子，我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。

select connect_by_root child,sys_connect_by_path(child,'/') path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ROOT PATH
------------------------------ --------
Asia /Asia
Asia /Asia/China
Asia /Asia/China/Beijing
Asia /Asia/Japan
Asia /Asia/Japan/Osaka
Asia /Asia/Japan/Tokyo
Australia /Australia
Australia /Australia/New South Wales
Australia /Australia/New South Wales/Sydney
Europe /Europe
Europe /Europe/United Kingdom
Europe /Europe/United Kingdom/England
Europe /Europe/United Kingdom/England/London
North America /North America
North America /North America/Canada
North America /North America/Canada/Ontario
North America /North America/Canada/Ontario/Ottawa
North America /North America/Canada/Ontario/Toronto
North America /North America/USA
North America /North America/USA/California
North America /North America/USA/California/Redwood Shores
 
在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列――CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。如下例所示：

create table hier2
(parent number,
 child  number
);

insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);

select connect_by_iscycle,sys_connect_by_path(child,'/') path
from hier2
start with parent is null
connect by nocycle prior child = parent;

CONNECT_BY_ISCYCLE PATH
------------------ -------
0 /1
0 /1/2
1 /1/2/3
--------------------------------------------------------------------------------------------------------

-END-

Reference to:
========================================================================================================
1.Oracle10g中新型层次查询选项简介(http://www.erp100.com/html/43/2743-6759345.html)
2.Oracle 中使用层次查询方便处理财务报表(http://blog.csdn.net/wqsmiling/archive/2005/06/14/394404.aspx)
3.树结构和它的专用函数SYS_CONNECT_BY_PATH(http://blog.oracle.com.cn/html/83/t-122083.html)
4.START WITH and CONNECT BY in Oracle SQL(http://www.adp-gmbh.ch/ora/sql/connect_by.html)