目錄：
=========================================
1.使用偽列Level顯示表中節(jié)點(diǎn)的層次關(guān)系
2.統(tǒng)計(jì)表中節(jié)點(diǎn)的層數(shù)
3.統(tǒng)計(jì)表中各個(gè)層次的節(jié)點(diǎn)數(shù)量
4.查找表中各個(gè)層次的節(jié)點(diǎn)信息
5.在Start with中使用子查詢
6.判斷節(jié)點(diǎn)和節(jié)點(diǎn)之間是否具有層次關(guān)系
7.刪除級(jí)聯(lián)表中的子樹
一、使用偽列Level顯示表中節(jié)點(diǎn)的層次關(guān)系：
Oracle9i對(duì)級(jí)聯(lián)查詢的支持不僅在于提供了像Start with...Connect by這樣的子句供我們很方便地執(zhí)行查詢，而且還提供了一個(gè)偽列(Pseudocolumn): Level。這個(gè)偽列的作用是在遞歸查詢的結(jié)果中用來表示節(jié)點(diǎn)在整個(gè)結(jié)構(gòu)中所處的層次。下面我們來看看實(shí)際的例子：
還是上次那個(gè)employee表，現(xiàn)在我們要在上次的需求上面增加點(diǎn)小玩意：輸出每個(gè)節(jié)點(diǎn)的層次值，看如下SQL：
SQL> select level, id, emp_name, manager_id from employee start with id = 2 connect by prior id = ma
nager_id order by id;
LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
1 2 mark 1
2 4 tom 2
2 5 paul 2
3 7 ben 4
SQL>
我們可以看到在LEVEL列，輸出了1,2,2,3的值，這就是Oracle為我們提供的一個(gè)偽列。此偽列只能用在start with...connect by子句中，下面我們來看另一種方式是否可行：
SQL> select level, p.* from (select * from employee start with id = 2 connect by prior id = manager_
id order by id) p;
LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
0 2 mark 1
0 4 tom 2
0 5 paul 2
0 7 ben 4
SQL>
可以看到Level列的值全部變成了0，可見在這里Oracle并不認(rèn)為虛表P里面的數(shù)據(jù)是“層次關(guān)系”，因而對(duì)于Level都返回0
二、統(tǒng)計(jì)表中節(jié)點(diǎn)的層數(shù)：
假設(shè)現(xiàn)在我們想看一下當(dāng)前employee表中員工總共分為幾個(gè)級(jí)別，我們應(yīng)該如何做呢？請(qǐng)看下面的SQL
SQL> select * from employee;
ID EMP_NAME MANAGER_ID
---------- -------------------- ----------
1 king
2 mark 1
3 bob 1
4 tom 2
5 paul 2
6 jack 3
7 ben 4
7 rows selected.
SQL>
SQL>
SQL> select count(level) from employee start with manager_id is null connect by prior id = manager_i
d;
COUNT(LEVEL)
------------
7
SQL>
SQL> select count(distinct level) from employee start with manager_id is null connect by prior id =
manager_id;
COUNT(DISTINCTLEVEL)
--------------------
4
從這里我們可以看到，在統(tǒng)計(jì)的時(shí)候一定要使用distinct關(guān)鍵字，否則得到的錯(cuò)誤的結(jié)果。
三、統(tǒng)計(jì)表中各個(gè)層次的節(jié)點(diǎn)數(shù)量：
假設(shè)我們想知道employee表中每個(gè)級(jí)別的員工數(shù)量，我們應(yīng)該如何做呢--對(duì)了，使用Level和group by子句了
SQL> select level, count(level) from employee start with manager_id is null connect by prior id = ma
nager_id group by level;
LEVEL COUNT(LEVEL)
---------- ------------
1 1
2 2
3 3
4 1
四、查找表中各個(gè)層次的節(jié)點(diǎn)信息：
上面的例子很簡單，我們看到Level可以用在group by子句中，現(xiàn)在我們更進(jìn)一步，查看指定層次的員工信息，比如說我現(xiàn)在打算查看Level=2的所有員工的記錄，應(yīng)該如何做呢？很自然地我們想到了第一個(gè)SQL語句：
SQL> select level, id, emp_name, manager_id from employee where level >= 2;
no rows selected
很奇怪吧，這這里level關(guān)鍵字就不起作用了，這是因?yàn)閘evel偽列只能在和start with...connect by子句結(jié)合時(shí)才能發(fā)揮作用，就想上面的統(tǒng)計(jì)各層節(jié)點(diǎn)數(shù)量一樣，于是我們又立馬想到了第二個(gè)SQL語句：
select *
from (select level, id, emp_name, manager_id
from employee
start with manager_id is null
connect by prior id = manager_id
order by id) p
where p.level = 2
看起來這個(gè)句子沒有什么問題吧，實(shí)際執(zhí)行的效果如何呢？我們?cè)赟QL*PLUS下執(zhí)行，結(jié)果卻是報(bào)錯(cuò)：
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
很郁悶！為什么會(huì)報(bào)p.level不可識(shí)別呢？這是因?yàn)閘evel是Oracle的偽列，并不屬于任何一個(gè)表，我們必須使用別名把這個(gè)偽列“偽裝”成一個(gè)實(shí)際的列，現(xiàn)在我們看第三個(gè)語句，注意語句高亮處。
SQL> select *
2 from (select level emp_level , id, emp_name, manager_id
3 from employee
4 start with manager_id is null
5 connect by prior id = manager_id
6 order by id) p
7 where p.emp_level = 2;
EMP_LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
2 2 mark 1
2 3 bob 1
SQL>
這次終于搞定了！不過實(shí)際上我們有更簡單的解決方法，請(qǐng)看第四個(gè)SQL語句：
SQL> select level, id, emp_name, manager_id
2 from employee
3 where level = 2
4 start with manager_id is null
5 connect by prior id = manager_id
6 order by id;
LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
2 2 mark 1
2 3 bob 1
上面我們是查看某個(gè)層次的所有節(jié)點(diǎn)信息，現(xiàn)在我們打算看看所有層次的節(jié)點(diǎn)信息，而且要求用一種直觀的信息顯示出來。下面的例子演示了如何使用空格縮進(jìn)的方式來直觀顯示節(jié)點(diǎn)之間的層次關(guān)系：
SQL> select level, id, lpad(' ', 2 * (level - 1)) || emp_name name, manager_id
2 from employee
3 start with manager_id is null
4 connect by prior id = manager_id;
LEVEL ID NAME MANAGER_ID
---------- ---------- -------------------- ----------
1 1 king
2 2 mark 1
3 4 tom 2
4 7 ben 4
3 5 paul 2
2 3 bob 1
3 6 jack 3
7 rows selected.
請(qǐng)注意這里的lpad函數(shù)的作用，正是它利用了層次和空格進(jìn)行縮進(jìn)，讓我們可以很直觀地從NAME字段對(duì)齊方式就知道各個(gè)節(jié)點(diǎn)的層次關(guān)系。如果我們需要過濾其中的某些節(jié)點(diǎn)，只需要將where條件加在start with前面就可以了(注意必須是前面，否則會(huì)報(bào)語法錯(cuò)誤)。
五、在Start with中使用子查詢：
在前面我們看到的例子中，start with的值都是一個(gè)固定的內(nèi)容，但有些時(shí)候查詢的起始點(diǎn)并不容易確定，比如：查詢工號(hào)最小的員工節(jié)點(diǎn)及其子節(jié)點(diǎn)，這個(gè)時(shí)候工號(hào)最小很明顯是一個(gè)查詢的條件，需要我們先通過執(zhí)行一個(gè)查詢得到確定的值，再作為查詢的起點(diǎn)。請(qǐng)看例子：
SQL> select level, id, lpad(' ', 2 * (level - 1)) || emp_name name, manager_id
2 from employee
3 start with id = (select min(id) from employee)
4 connect by prior id = manager_id;
LEVEL ID NAME MANAGER_ID
---------- ---------- -------------------- ----------
1 1 king
2 2 mark 1
3 4 tom 2
4 7 ben 4
3 5 paul 2
2 3 bob 1
3 6 jack 3
7 rows selected.
六、判斷節(jié)點(diǎn)和節(jié)點(diǎn)之間是否具有層次關(guān)系：
在日常工作中除了查詢節(jié)點(diǎn)的信息之外，另一個(gè)常見的應(yīng)用就是判斷某個(gè)節(jié)點(diǎn)和另外一個(gè)/些節(jié)點(diǎn)之間是否具有層次關(guān)系。例如我想知道員工mark是不是員工jack的領(lǐng)導(dǎo)(直接或間接的都可以)，我應(yīng)該怎么做呢？
考慮到start with...connect by會(huì)返回一棵節(jié)點(diǎn)樹，假如節(jié)點(diǎn)數(shù)上沒有jack節(jié)點(diǎn)，那么說明mark并不是jack的直接或間接領(lǐng)導(dǎo)，如果找到那說明mark是jack的父節(jié)點(diǎn)。方法簡單
SQL> select level,
2 id,
3 lpad(' ', 2 * (level - 1)) || emp_name employee_name,
4 manager_id
5 from employee
6 where emp_name = 'jack'
7 start with emp_name = 'mark'
8 connect by prior id = manager_id;
no rows selected
七、刪除級(jí)聯(lián)表中的子樹：
假設(shè)現(xiàn)在employee表中的mark及其下屬員工離職，那么我們?yōu)榱司S護(hù)數(shù)據(jù)的完整性，必須將mark及其下屬員工的節(jié)點(diǎn)都刪除，有了start with...connect by和level我們就可以輕松地做到這一點(diǎn)了。
【1】按名稱刪除節(jié)點(diǎn)樹：
SQL> delete from employee
2 where id in (select id
3 from employee
4 start with emp_name = 'mark'
5 connect by prior id = manager_id);
4 rows deleted.
【2】按層次刪除節(jié)點(diǎn)樹：
從上面的例子我們知道只需要在第一個(gè)SQL的基礎(chǔ)上改變一下：使用level區(qū)分節(jié)點(diǎn)的層次就做到了。
參考資料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)
-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。
posted on 2008-06-12 17:45
Paul Lin 閱讀(3959)
評(píng)論(0) 編輯 收藏 所屬分類:
Oracle 開發(fā)