SQL-Start with connect by prior用法

本文详细解析了Oracle PL/SQL中Connectby语句的使用方法,包括语法结构、测试环境搭建及不同场景下的查询效果对比,强调了PRIOR关键字位置对查询结果的影响。

參考資料
https://dotblogs.com.tw/jeff-yeh/2009/05/20/8489
http://tomkuo139.blogspot.tw/2009/06/oracle-plsql-treeview.html
https://read01.com/0zGL7o.html

最近與C大研究了Connect by,發現還是有些疑問,開始上網找答案

語法架構

1

2

3

4

5

6

SELECT COL1, COL2,.. .

  FROM TABLE [

 WHERE CONDITION.. . ]

 START WITH 要放在最上層的條件

CONNECT BY [ PRIOR ] CHILD_COLUMN = PARENT_COLUMN [

 ORDER [ SIBLINGS ] BY SORT .. . ]

若 prior 有提供,則會查詢出本階與其所有下階資料。
若 prior 無提供,則只查詢出本階資料。
若 siblings 有提供,則會按父子階層排序,再按指定欄位排序。
若 siblings 無提供,則會按指定欄位排序。
Child_Column = Parent_Column,視為 "Child 的父階是哪個"。
查詢會先按 start with 展開 Treeview,再過濾 where 條件。

 


 

架設測試環境
CREATE TABLE IT_CONNECT_BY
(
  EMP   VARCHAR2(20),
  NAME  VARCHAR2(20),
  EMAIL VARCHAR2(20),
  BOSS  VARCHAR2(20)
)

測試資料
EMPNAMEEMAILBOSS
A0001Jamesjames@aaa.com 
A0002Porzporz@aaa.comA0001
A0003Sandysandy@aaa.comA0002
A0004Gracegrace@aaa.comA0003

1

2

3

4

5

--沒有 PRIOR 尋找 A0003 的主管

SELECT *

  FROM IT_CONNECT_BY CB

CONNECT BY CB.EMP = CB.BOSS

 START WITH CB.EMP = 'A0003';

結果
EMPNAMEEMAILBOSS
A0003Sandysandy@aaa.comA0002

1

2

3

4

5

6

--有 PRIOR 尋找 A0003 的主管

--PRIOR 在左邊

SELECT *

FROM IT_CONNECT_BY CB

CONNECT BY PRIOR CB.BOSS = CB.EMP

START WITH CB.EMP = 'A0003';

結果
EMPNAMEEMAILBOSS
A0003Sandysandy@aaa.comA0002
A0002Porzporz@aaa.comA0001
A0001Jamesjames@aaa.com 

1

2

3

4

5

6

--有 PRIOR 尋找 A0003 的主管

--PRIOR 在右邊

SELECT *

  FROM IT_CONNECT_BY CB

CONNECT BY CB.BOSS = PRIOR CB.EMP

 START WITH CB.EMP = 'A0003';

結果
EMPNAMEEMAILBOSS
A0003Sandysandy@aaa.comA0002
A0004Gracegrace@aaa.comA0003

最後發現 PRIOR 的位置,會決定是由A0003出發向上展或者向下

 

`START WITH CONNECT BY PRIOR` 是Oracle数据库中用于层次查询的语法。它用于查询具有层次结构的数据,例如树形结构或父子关系的数据。以下是一些关键点和使用示例: 1. **层次查询的基本语法**: ```sql SELECT column1, column2, ... FROM table_name START WITH condition1 CONNECT BY PRIOR condition2; ``` 2. **参数解释**: - `START WITH`:指定层次查询的起始条件,即根节点。 - `CONNECT BY PRIOR`:指定父子关系,其中`PRIOR`关键字用于引用父节点。 3. **示例**: 假设有一个表`employees`,结构如下: ```sql CREATE TABLE employees ( employee_id NUMBER, name VARCHAR2(100), manager_id NUMBER ); ``` 其中,`employee_id`是员工唯一标识,`manager_id`是其直接上级的`employee_id`。 查询所有员工及其上级关系: ```sql SELECT employee_id, name, manager_id FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; ``` 这个查询的逻辑是:从没有上级的员工(根节点)开始,递归查找其下级员工。 4. **使用`LEVEL`伪列**: `LEVEL`伪列用于表示当前节点在层次结构中的深度。 ```sql SELECT LEVEL, employee_id, name, manager_id FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; ``` 5. **使用`CONNECT_BY_ROOT`**: `CONNECT_BY_ROOT`用于获取根节点的值。 ```sql SELECT CONNECT_BY_ROOT name AS root_manager, employee_id, name, manager_id FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; ``` 通过使用`START WITH CONNECT BY PRIOR`,可以方便地查询和操作具有层次结构的数据。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值