oracle层级关系按列存储_[转载]Oracle层次查询及应用(start with connect by)

摘要：本文将根据对层次查询语句简单例子的说明来理解应用，并举例实际的应用案例。

========================================================================================================

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,