詳解SQL Server 2008中CTE遞歸查詢的實(shí)現(xiàn)

2010-10-15 13:35:10來源：作者：

今天基本搞清楚SQL Server中遞歸查詢的實(shí)現(xiàn)方式，So，先記錄下來。不過呢，個人覺得SQL Server的遞歸查詢相對于Oracle中的遞歸查詢更加難以理解。

今天基本搞清楚SQL Server中遞歸查詢的實(shí)現(xiàn)方式，So，先記錄下來。不過呢，個人覺得SQL Server的遞歸查詢相對于Oracle中的遞歸查詢更加難以理解。

從SQL Server 2005開始，我們可以直接通過CTE來支持遞歸查詢，這對查詢樹形或?qū)哟谓Y(jié)構(gòu)的數(shù)據(jù)很有用。CTE即公用表表達(dá)式，雖然不恰當(dāng)，但你可以將它看做成一個臨時命名的結(jié)果集合。

我們先建立一個示例表，名稱為MENU，表示菜單的層次結(jié)構(gòu):

  1. CREATE TABLE MENU   
  2.  (  
  3.     name nvarchar(50) NOT NULL PRIMARY KEY,  
  4.     senior nvarchar(50) NULL 
  5. );  
  6.    
  7.  INSERT INTO MENU values 
  8.     ('文件',NULL),  
  9.     ('新建','文件'),  
  10.     ('項(xiàng)目','新建'),  
  11.     ('使用當(dāng)前連接查詢','新建'); 

表示的菜單層次如下：

  1. 文件   
  2.     新建   
  3.         項(xiàng)目   
  4.         使用當(dāng)前連接查詢  
  5.  
  6. OK，先看CTE的語法：   
  7.   WITH CTE名稱(字段列表)   
  8.   AS   
  9.   (   
  10.     查詢語句   
  11.   ) 

 

例如：

  1. WITH lmenu(name,senior)   
  2. as   
  3. (   
  4.     SELECT name,senior from menu   
  5. ) 

我們定義了一個名稱為lmenu的CTE，這樣我們后續(xù)即可直接使用lmenu來查詢，如：

  1. SELECT * FROM lmenu 

如果我們在定義CTE的查詢語句中直接引用CTE表本身，則會形成遞歸查詢，當(dāng)然遞歸查詢具有自己的特殊結(jié)構(gòu)，下面的SQL通過遞歸查詢獲取每個菜單的層次深度：

  1. WITH lmenu(name,senior,level) as 
  2.  (  
  3.     SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL 
  4.     UNION ALL 
  5.     SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b  
  6.     where a.senior = b.name 
  7. )  
  8.  
  9. SELECT *  from lmenu 

結(jié)果：

 

  1. name               senior              level   
  2. ----------------------------------- -----------   
  3. 文件                NULL                 0   
  4. 新建                文件                  1   
  5. 使用當(dāng)前連接查詢      新建                 2   
  6. 項(xiàng)目                新建                 2 

注意查詢定義語句，它由兩條查詢語句構(gòu)成，其中

  1. SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL 

稱為定位成員，SQL Server通過此語句來判斷是否繼續(xù)進(jìn)行遞歸。

  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
  2.     where a.senior = b.name 

稱之為遞歸成員，其特征為from子句中引用了CTE對象自身。

遞歸CTE具有一些限制條件(引自MSDN)：

至少有一個定位點(diǎn)成員和一個遞歸成員，當(dāng)然，你可以定義多個定位點(diǎn)成員和遞歸成員，但所有定位點(diǎn)成員必須在遞歸成員的前面

定位點(diǎn)成員之間必須使用UNION ALL、UNION、INTERSECT、EXCEPT集合運(yùn)算符，最后一個定位點(diǎn)成員與遞歸成員之間必須使用UNION ALL，遞歸成員之間也必須使用UNION ALL連接

定位點(diǎn)成員和遞歸成員中的字段數(shù)量和類型必須完全一致

遞歸成員的FROM子句只能引用一次CTE對象

遞歸成員中不允許出現(xiàn)下列項(xiàng) 

  1. SELECT DISTINCT   
  2. GROUP BY   
  3. HAVING   
  4. 標(biāo)量聚合   
  5. TOP   
  6. LEFT、RIGHT、OUTER JOIN（允許出現(xiàn) INNER JOIN）   
  7. 子查詢 

CTE遞歸查詢的執(zhí)行方式：

遞歸的終止依賴于定位點(diǎn)成員的，如果理解了這一點(diǎn)，也就理解了遞歸查詢的執(zhí)行方式。

我們來看上例的執(zhí)行執(zhí)行過程：

  1. SELECT * FROM lmenu 

這條語句進(jìn)入遞歸查詢

  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
  2.     where a.senior = b.name 

作為最外層的語句，顯然遞歸的第一層應(yīng)該根據(jù)MENU表的記錄來循環(huán)(如果查詢執(zhí)行計(jì)劃，這表示一個嵌套循環(huán))，假設(shè)menu表中查詢出的記錄順序如下：

  1. name                                       senior   
  2. --- --------------------------------------------------   
  3. 文件                                          NULL   
  4. 新建                                            文件   
  5. 使用當(dāng)前連接查詢                       新建   
  6. 項(xiàng)目                                            新建 

第一條記錄：

首先判斷是否進(jìn)入遞歸，由于 文件包含在定位點(diǎn)成員結(jié)果集中，不符合遞歸條件，所以不進(jìn)入遞歸，直接返回從定位點(diǎn)成員集合中返回記錄：

  1. select name,senior,0 level from menu where senior is null and name='文件' 

 

  1. name                    senior           level   
  2. --------------- ------------------------ -----------   
  3. 文件                      NULL                0 

第二條記錄：

即NAME = '新建', 定位點(diǎn)成員結(jié)果集中沒有該記錄，將進(jìn)入遞歸：

將當(dāng)前行的值帶入遞歸成員：

  1. SELECT A.NAME,A.SENIOR,b.level+1 level FROM MENU A,lmenu b   
  2.     where a.senior = b.name   
  3.         AND a.senior = '文件'   
  4.         AND a.name='新建' 

由于遞歸的關(guān)聯(lián)條件是a.senior = b.name，所以b.name='文件'，以此條件進(jìn)入下級遞歸,這實(shí)際上就是第一條記錄的情況，由于name='文件'符合定位點(diǎn)條件，所以將終止遞歸，如果我們用子查詢來替換掉lmenu遞歸成員，第二條記錄的查詢語句實(shí)際為：

  1. SELECT a.name,a.senior,b.level+1 from menu a, (  
  2.   select name,senior,0 level  from menu 
  3. where senior is null and name='文件' 
  4. ) b  
  5. where a.senior=b.name 
  6.     and a.senior = '文件' 
  7.     and a.name='新建' 

name            senior                    level
--------------------- --------------------------
新建             文件                           1

第三條記錄：

NAME='使用當(dāng)前連接查詢'，同樣不符合定位點(diǎn)條件，將進(jìn)入遞歸：

  1. SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b   
  2.     where a.senior = b.name   
  3.           AND a.senior = '新建'   
  4.           AND a.name = '使用當(dāng)前連接查詢' 

同樣，代入當(dāng)前記錄條件，下級遞歸b.name='新建',由于'新建'還不符合定位點(diǎn)條件，所以還將繼續(xù)遞歸，及l(fā)menu b表示子查詢：

  1. select c.name,c.senior,d.level+1 level from menu c,lmenu d   
  2. where c.senior = d.name   
  3.      and c.name = '新建'   
  4.      and c.senior = '文件' 

替換成上述語句后，d.name='文件'，將再次判斷是否需要繼續(xù)遞歸，由于'文件'符合終止遞歸條件，所以將終止遞歸。

我們用子查詢表示第三條記錄的遞歸過程如下：

  1. SELECT a.name,a.senior,b.level+1 level FROM menu A,(  
  2.         select c.name,c.senior,d.level+1 level from menu c,(  
  3.           select name,senior,0 level from menu where senior is null and name='文件' 
  4.     ) d  
  5.     where c.senior = d.name 
  6.         and c.name = '新建' 
  7.         and c.senior = '文件'     
  8. ) b  
  9. where a.senior = b.name 
  10.     and a.senior = '新建' 
  11.     and a.name = '使用當(dāng)前連接查詢' 

name                                     senior                    level

--------------------------------------------------------- -----------

使用當(dāng)前連接查詢                    新建                       2

第四條記錄與第三條記錄的遞歸層次完全一樣。

原文標(biāo)題：SQL Server 2008中的CTE遞歸查詢

鏈接：http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html

關(guān)鍵詞：SQLServerMSSQL