SQL遞迴查詢（SqlServer/ORACLE遞迴查詢）[文法差異分析]

在 SQLSERVER2005以後，mssql開始有了遞迴查詢的方法了。比較起最開始寫預存程序或者寫function的方式。這樣的方式更加簡便靈活的。

而oracle也有內建的樹形結構遞迴查詢方法，connect by

下面我自己寫的一段SQL，簡單注釋下CTE共用運算式的一些用法。 實現對樹狀結構的根節點和子節點的查詢。

代碼------------------------------------------------------------------------
-- author:jc_liumangtu(【DBA】小七)
-- date:    2010-03-30 15:09:42
-- version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
--     Oct 14 2005 00:33:37
--     Copyright (c) 1988-2005 Microsoft Corporation
--     Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
------------------------------------------------------------------------
use test
set nocount on
if object_id('Dept','U') is not null
drop table Dept
go
create table Dept(ID int,ParentID int,Name varchar(20))  
insert into Dept select 1,0,'AA'
insert into Dept select 2,1,'BB'
insert into Dept select 3,1,'CC' 
insert into Dept select 4,2,'DD' 
insert into Dept select 5,3,'EE' 
insert into Dept select 6,0,'FF'
insert into Dept select 7,6,'GG'
insert into Dept select 8,7,'HH'
insert into Dept select 9,7,'II'
insert into Dept select 10,7,'JJ'
insert into Dept select 11,9,'KK'

go  
SELECT * FROM Dept;

--查詢樹狀結構某節點的上級所有根節點。
with cte_root(ID,ParentID,NAME)
as
(
    --起始條件
    select ID,ParentID,NAME
    from Dept
    where Name = 'II'   --列出子節點查詢條件
    union all
    --遞迴條件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join
    cte_root b          --執行遞迴，這裡就要理解下了
    on a.ID=b.ParentID  --根據基礎資料表條件查詢子節點(a.ID)，通過CTE遞迴找到其父節點(b.ParentID)。
)                       --可以和下面查詢子節點的cte_child對比。
select * from cte_root ;

--查詢樹狀結構某節點下的所有子節點。
with cte_child(ID,ParentID,NAME)
as
(
    --起始條件
    select ID,ParentID,NAME
    from Dept
    where Name = 'II' --列出父節點查詢條件
    union all
    --遞迴條件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join
    cte_child b
    on ( a.ParentID=b.ID)  --根據查詢到的父節點(a.Parent)，通過CTE遞迴查詢出其子節點(b.ID)
)

select * from cte_child --可以改變之前的查詢條件'II'再測試結果

ID          ParentID    Name
----------- ----------- --------------------
1           0           AA
2           1           BB
3           1           CC
4           2           DD
5           3           EE
6           0           FF
7           6           GG
8           7           HH
9           7           II
10          7           JJ
11          9           KK

ID          ParentID    NAME
----------- ----------- --------------------
9           7           II
7           6           GG
6           0           FF

ID          ParentID    NAME
----------- ----------- --------------------
9           7           II
11          9           KK
複製代碼

在msdn中介紹了CTE的一些限制：

至少有一個錨點成員和一個遞迴成員，當然，你可以定義多個錨點成員和遞迴成員，但所有錨點成員必須在遞迴成員的前面
錨點成員之間必須使用UNION ALL、UNION、INTERSECT、EXCEPT集合運算子，最後一個錨點成員與遞迴成員之間必須使用UNION ALL，遞迴成員之間也必須使用UNION ALL串連
錨點成員和遞迴成員中的欄位數量和類型必須完全一致
遞迴成員的FROM子句只能引用一次CTE對象
遞迴成員中不允許出現下列項
    SELECT DISTINCT
    GROUP BY
    HAVING
    標量彙總
    TOP
    LEFT、RIGHT、OUTER JOIN（允許出現 INNER JOIN）
    子查詢

接下來介紹下Oracle裡面的遞迴查詢方法，connect by prior ，start with。相對於SqlServer來說，Oracle的方法更加簡潔明了，簡單易懂。很容易就讓人理解其用法。借來我會用和上面SqlServer同樣的資料和結構進行代碼示範，和對一些關鍵字的用法進行闡述。

SELECT …..

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH]；

下面是代碼測試：

代碼--建立表
create table Dept(ID int,ParentID int,Name varchar(20));
--增加測試資料，和上面的SqlServer資料相同
insert into Dept  select 1,0,'AA' from dual;
insert into Dept  select 2,1,'BB' from dual;
insert into Dept  select 3,1,'CC'  from dual;
insert into Dept  select 4,2,'DD'  from dual;
insert into Dept  select 5,3,'EE'  from dual;
insert into Dept  select 6,0,'FF' from dual;
insert into Dept  select 7,6,'GG' from dual;
insert into Dept  select 8,7,'HH' from dual;
insert into Dept  select 9,7,'II' from dual;
insert into Dept  select 10,7,'JJ' from dual;
insert into Dept  select 11,9,'KK' from dual;
commit;

--查詢根節點（父節點）
select * from Dept            --查詢基礎資料表
connect by id=prior parentid  --connect by就是欄位的關聯關鍵字，prior有預先和前的意思，則是放在哪個欄位前，哪個就是遞迴的上一層
start with name='II';         --start with則是遞迴的起始位置，也可以用id或者是parentid。可以修改II的值測試其他資料。

--查詢結果
ID    PARENTID    NAME
9      7            II
7      6            GG
6      0            FF

--查詢子節點

select * from Dept
connect by prior id=parentid  --同樣的語句，僅僅改變prior位子，就發生了指向性的變化，就是這裡id為遞迴上一層。
start with name='II';

--查詢結果
ID    PARENTID    NAME
9       7            II
11    9            KK

--測試結果和SqlServer一致，語句卻更精練，簡潔易懂。複製代碼

經過分別對SqlServer和Oracle的測試，發現兩個資料庫都很好的支援遞迴查詢，相比之下Oracle的遞迴查詢語句更加簡練易懂，更容易讓人理解。

在做測試的時候，SqlServer更方便的產生測試資料，上面的代碼可以複製後重複執行，而Oracle複製執行一次可以，重複執行的話，在執行建立表的工作，就會報錯了，原因很簡單，Oracle要判斷表存在然後刪除後重建的工作用代碼實現很麻煩。而SqlServer只需要if後drop表再create就搞定。所以兩種資料庫各有千秋。