在 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)
where Name = 'II' --列出父節點查詢條件
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
<a href="http://www.cnblogs.com/dba_xiaoqi/archive/2010/10/31/1865303.html">複制代碼</a>
在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';
9 7 II
11 9 KK
--測試結果和SqlServer一緻，語句卻更精練，簡潔易懂。
經過分别對SqlServer和Oracle的測試，發現兩個資料庫都很好的支援遞歸查詢，相比之下Oracle的遞歸查詢語句更加簡練易懂，更容易讓人了解。
在做測試的時候，SqlServer更友善的産生測試資料，上面的代碼可以複制後重複執行，而Oracle複制執行一次可以，重複執行的話，在執行建立表的工作，就會報錯了，原因很簡單，Oracle要判斷表存在然後删除後重建的工作用代碼實作很麻煩。而SqlServer隻需要if後drop表再create就搞定。是以兩種資料庫各有千秋。