在db2和oracle中的對樹的遞歸查詢語句。
表結(jié)構(gòu)：
create table? MAIN_NODE (
MLA_ID????? ?????????INTEGER???? not null??????? ,?? //節(jié)點ID
MLA_ROOTID????????? INTEGER,?? ???????????????????//根節(jié)點ID
MLA_PARENTID??????? INTEGER,??? ??????????????????//父節(jié)點ID
MLA_NAME??????????? VARCHAR2(50), ???????????????//節(jié)點名稱
constraint P_mlaid primary key (MLA_ID) );
MLA_ID ??MLA_PARENTID ?MLA_ROOTID?? MLA_NAME
1? ???????? 0??? ??????????? ???0??? ?????????父節(jié)點1?????
2? ???????? 1??? ??????????? ???0??? ????????（父節(jié)點1的/）子節(jié)點1???
3?????? ??? ?2??? ???????????? ??0?? ?????????（父節(jié)點1的/子節(jié)點1的/）孫子節(jié)點1
4? ???????? 0??? ??????????? ???0??? ?????????父節(jié)點2?????
5? ???????? 4??? ?????????? ????0???? ???????（父節(jié)點2的/）子節(jié)點1???
樹結(jié)構(gòu)直觀圖：
根節(jié)點（0）
???? 父節(jié)點1（1）
?????????? （父節(jié)點1的/）子節(jié)點1（2）?
???????????????????? （父節(jié)點1的/子節(jié)點1的/）孫子節(jié)點1（3）
???? 父節(jié)點2（4）
?????????? （父節(jié)點2的/）子節(jié)點1（5）
語句要求的功能：實現(xiàn)給出一個父節(jié)點id然后得到該父節(jié)點下的所有子節(jié)點的信息Db2 的查詢語句：
WITH ?RPL (mla_parentid, mla_id, mla_name) AS
(
SELECT ROOT.mla_parentid, ROOT.mla_id, ROOT.mla_name FROM main_node ROOT WHERE ROOT.mla_id = ?
UNION ?ALL
SELECT
CHILD.mla_parentid, CHILD.mla_id, CHILD.mla_name FROM RPL PARENT,
main_node CHILD WHERE PARENT.mla_parentid = CHILD.mla_id
?)
SELECT DISTINCT mla_parentid, mla_id, mla_name FROM RPL ORDER BY mla_parentid, mla_id, mla_name
讓我們研究這個查詢語句：
- RPL 作為一個具有以下三列的虛擬表：mla_parentid, mla_id 和 mla_name。
- WITH 子句內(nèi)的第一個 SELECT 語句是初始化表。它只執(zhí)行一次。它的結(jié)果形成虛擬表的初始內(nèi)容以作為遞歸的種子。在上面的示例中，種子是 mla_id 為 任意傳進去的參數(shù) 的一行或多行。
- 第
二個 SELECT 語句執(zhí)行多次。將種子作為輸入（JOIN 中的輔助表）傳遞給第二個 SELECT 語句以產(chǎn)生下一個行集合。將 JOIN
的結(jié)果添加（UNION ALL）到虛擬表的當前內(nèi)容中，并放回到其中以形成用于下一次傳遞的輸入。只要有行產(chǎn)生，這個過程就會繼續(xù)。
- 如果期望，虛擬表上最后的 SELECT 允許我們選擇遞歸查詢所產(chǎn)生的所有行或僅部分行。
Oracle的查詢語句：
?? select mla_parentid, mla_id, mla_name from main_node
start with mla_id=? connect by prior mla_id=mla_parentid
讓我們研究這個查詢語句：
- ? 本語句實際上是 start with ...connect by 的用法， start with 后面所跟的就是就是遞歸的種子。在上面的示例中，種子是 mla_id 為 任意傳進去的參數(shù)
- connect by 后面的"prior"如果缺省：則只能查詢到符合條件的起始行，并不進行遞歸查詢；
- connect by prior 后面所放的字段是有關(guān)系的，它指明了查詢的方向。如果后面放的是 mla_id=mla_parentid 則表明從本節(jié)點查向葉子節(jié)點；如果后面放的是 mla_parentid = mla_id則表明從根節(jié)點查向本節(jié)點；