【--SQL基礎-->層次化查詢(START BY ... CONNECT BY PRIOR)】
--======================================================
--SQL基礎-->層次化查詢(START BY ... CONNECT BY PRIOR)
--======================================================
層次化查詢,即樹型結構查詢,是SQL中經常用到的功能之一,通常由根節點,父節點,子節點,葉節點組成,其語法如下:
SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
LEVEL:為偽列,用於表示樹的層次
start_condition：層次化查詢的起始條件
prior_condition：定義父節點和子節點之間的關係
--使用start with ...connect by prior 從根節點開始遍歷
SQL> select empno,mgr,ename,job from emp
2 start with empno = 7839
3 connect by prior empno = mgr;
EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7839 KING PRESIDENT
7566 7839 JONES MANAGER
7788 7566 SCOTT ANALYST
7876 7788 ADAMS CLERK
7902 7566 FORD ANALYST
7369 7902 SMITH CLERK
7698 7839 BLAKE MANAGER
7499 7698 ALLEN SALESMAN
7521 7698 WARD SALESMAN
7654 7698 MARTIN SALESMAN
7844 7698 TURNER SALESMAN
EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7900 7698 JAMES CLERK
7782 7839 CLARK MANAGER
7934 7782 MILLER CLERK
14 rows selected.
樹型結構遍歷過程(透過上面的查詢來描述)
1).從根節點開始(即where_clause中的條件,如果為非根節點則分根節點作為根節點開始遍歷,如上例empno = 7839)
2).遍歷根節點(得到empno = 7839記錄的相關資訊)
3).判斷該節點是否存在由子節點，如果則訪問最左側未被訪問的子節點,轉到),否則下一步
如上例中prior_condition為empno = mgr,即子節點的mgr等於父節點的empno,在此時mgr為7839的記錄
4).當節點為葉節點，則訪問完畢,否則,轉到)
5).返回到該節點的父節點,轉到)
--偽列level的使用
--注意connect by prior empno = mgr 的理解
--prior表示前一條記錄,即下一條返回記錄的mgr應當等於前一條記錄的empno
SQL> select level,empno,mgr,ename,job from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 order by level;
LEVEL EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------- ---------
1 7839 KING PRESIDENT
2 7566 7839 JONES MANAGER
2 7698 7839 BLAKE MANAGER
2 7782 7839 CLARK MANAGER
3 7902 7566 FORD ANALYST
3 7521 7698 WARD SALESMAN
3 7900 7698 JAMES CLERK
3 7934 7782 MILLER CLERK
3 7499 7698 ALLEN SALESMAN
3 7788 7566 SCOTT ANALYST
3 7654 7698 MARTIN SALESMAN
LEVEL EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------- ---------
3 7844 7698 TURNER SALESMAN
4 7876 7788 ADAMS CLERK
4 7369 7902 SMITH CLERK
--獲得層次數
SQL> select count(distinct level) "Level" from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr;
Level
----------
4
--格式化層次查詢結果(使用左填充* level - 1個空格)
SQL> col Ename for a30
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'KING'
6 connect by prior empno = mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
LEVEL Ename JOB
---------- ------------------------------ ---------
3 JAMES CLERK
2 CLARK MANAGER
3 MILLER CLERK
14 rows selected.
--從非根節點開始遍歷(只需修改start with 中的條件即可)
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'SCOTT'
6 connect by prior empno = mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 ADAMS CLERK
--從下向上遍歷(交換connect by prior中的條件即可,使用mgr = empno)
--注意connect by prior mgr = empno 的理解
--prior表示前一條記錄,即下一條返回記錄的empno應當等於前一條記錄的mgr
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'SCOTT'
6 connect by prior mgr = empno;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 JONES MANAGER
3 KING PRESIDENT
--從下向上遍歷(也可以將prior置於等號右邊,得到相同的結果)
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'SCOTT'
6 connect by empno = prior mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 JONES MANAGER
3 KING PRESIDENT
--從層次查詢中刪除節點和分支
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 where ename != 'SCOTT' --透過where子句來過濾SCOTT使用者，但SCOTT的下屬ADAMS並沒有過濾掉
6 start with empno = 7839
7 connect by prior empno = mgr;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK
LEVEL Ename JOB
---------- -------------------- ---------
2 CLARK MANAGER
3 MILLER CLERK
13 rows selected.
--透過將過濾條件由where 子句的內容移動到connect by prior 子句中過濾掉SCOTT及其下屬
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 start with empno = 7839
6 connect by prior empno = mgr and ename != 'SCOTT';
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK
2 CLARK MANAGER
LEVEL Ename JOB
---------- -------------------- ---------
3 MILLER CLERK
12 rows selected.
--在層次化查詢中增加過濾條件或使用子查詢
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 where sal > 2500
6 start with empno = 7839
7 connect by prior empno = mgr
8 ;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
3 FORD ANALYST
2 BLAKE MANAGER
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 where sal > (select avg(sal) from emp)
6 start with empno = 7839
7 connect by prior empno = mgr ;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
3 FORD ANALYST
2 BLAKE MANAGER
2 CLARK MANAGER
6 rows selected.
轉載：http://blog.csdn.net/leshami/article/details/5616877
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2128491/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL的基礎查詢案例SQL
- Sql介紹 與 Sql基礎查詢SQL
- SQL語言基礎(子查詢)SQL
- SQL-基礎語法 - 條件查詢 - 模糊查詢SQL
- SQL語言基礎(高階查詢)SQL
- MySQL學習（三） SQL基礎查詢MySql
- 0629_層次查詢
- 淺析REGEXP_SUBSTR,PRIOR,CONNECT BY
- SQL-基礎語法-選擇查詢SQL
- SQL-基礎語法-查詢-別名SQL
- SQL基礎的查詢語句烈鉍SQL
- 【學習】SQL基礎-007-子查詢SQL
- 【學習】SQL基礎-006-多表連線查詢SQL
- 優化sql查詢速度優化SQL
- PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）SQL原始碼優化
- 資料庫基礎查詢--單表查詢資料庫
- SQL-基礎語法 - 條件查詢 - 邏輯運算SQL
- Oracle start with connect by PostgreSQL recursive cteOracleSQL
- SQL 基礎增、刪、改、查SQL
- 抽象SQL引數化查詢VK抽象SQL
- Sql Server 的引數化查詢SQLServer
- 十七、Mysql之SQL優化查詢MySql優化
- MySQL資料庫基礎——多表查詢：子查詢MySql資料庫
- CDA資料分析師 - SQL資料庫基礎 查詢&連線SQL資料庫
- Django：查詢基礎語法Django
- oracle資料庫sql查詢檢視第二次查詢很慢Oracle資料庫SQL
- SQL查詢的：子查詢和多表查詢SQL
- openGauss SQL引擎（下）——查詢最佳化SQL
- 《MySQL 基礎篇》四：查詢操作MySql
- mysql查詢表基礎資訊MySql
- ElasticSearch基礎及查詢語法Elasticsearch
- Linux基礎命令---查詢程式idLinux
- MySQL（三）DQL之基礎查詢MySql
- mysql基礎 查詢某屬性值出現次數大於1MySql
- SQL--查詢SQL
- SQL 聚合查詢SQL
- 原生SQL查詢SQL
- 多表聯合查詢 - 基於註解SQLSQL
- 01 | 基礎架構：一條SQL查詢語句是如何執行的？架構SQL