程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 2008中的CTE遞歸查詢

SQL Server 2008中的CTE遞歸查詢

編輯:關於SqlServer

 基本搞清楚SQL Server中遞歸查詢的實現方式,So,先記錄下來。不過呢,個人覺得SQL Server的遞歸查詢相對於Oracle中的遞歸查詢更加難以理解困惑。 

從SQL Server 2005開始,我們可以直接通過CTE來支持遞歸查詢,這對查詢樹形或層次結構的數據很有用。CTE即公用表表達式,雖然不恰當,但你可以將它看做成一個臨時命名的結果集合。
我們先建立一個示例表,名稱為MENU,表示菜單的層次結構:

顯示行號 復制代碼 ? SQL
  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.     ('項目','新建'),
  11.     ('使用當前連接查詢','新建');

表示的菜單層次如下:

文件
    新建
        項目
        使用當前連接查詢

OK,先看CTE的語法:
  WITH CTE名稱(字段列表)
  AS
  (
    查詢語句
  )
例如:
WITH lmenu(name,senior)
as
(
    SELECT name,senior from menu
)

我們定義了一個名稱為lmenu的CTE,這樣我們後續即可直接使用lmenu來查詢,如:
SELECT * FROM lmenu

如果我們在定義CTE的查詢語句中直接引用CTE表本身,則會形成遞歸查詢,當然遞歸查詢具有自己的特殊結構,下面的SQL通過遞歸查詢獲取每個菜單的層次深度:

顯示行號 復制代碼 ? 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

結果:

name                                               senior                                             level
-------------------------------------------------- -------------------------------------------------- -----------
文件                                                 NULL                                               0
新建                                                 文件                                                 1
使用當前連接查詢                               新建                                                 2
項目                                                 新建                                                 2

 

注意查詢定義語句,它由兩條查詢語句構成,其中

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

稱為定位成員,SQL Server通過此語句來判斷是否繼續進行遞歸。
語句

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

稱之為遞歸成員,其特征為from子句中引用了CTE對象自身。
遞歸CTE具有一些限制條件(引自MSDN):

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

CTE遞歸查詢的執行方式:
遞歸的終止依賴於定位點成員的,如果理解了這一點,也就理解了遞歸查詢的執行方式。
我們來看上例的執行執行過程:

SELECT * FROM lmenu

這條語句進入遞歸查詢

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

作為最外層的語句,顯然遞歸的第一層應該根據MENU表的記錄來循環(如果查詢執行計劃,這表示一個嵌套循環),假設menu表中查詢出的記錄順序如下:

name                                               senior
-------------------------------------------------- --------------------------------------------------
文件                                                 NULL
新建                                                 文件
使用當前連接查詢                               新建
項目                                                 新建

 

第一條記錄:
首先判斷是否進入遞歸,由於 文件包含在定位點成員結果集中,不符合遞歸條件,所以不進入遞歸,直接返回從定位點成員集合中返回記錄:

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

name                                               senior                                             level
-------------------------------------------------- -------------------------------------------------- -----------
文件                                                 NULL                                               0


第二條記錄:
即NAME = '新建', 定位點成員結果集中沒有該記錄,將進入遞歸:
將當前行的值帶入遞歸成員:

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

 

由於遞歸的關聯條件是a.senior = b.name,所以b.name='文件',以此條件進入下級遞歸,這實際上就是第一條記錄的情況,由於name='文件'符合定位點條件,所以將終止遞歸,如果我們用子查詢來替換掉lmenu遞歸成員,第二條記錄的查詢語句實際為:

顯示行號 復制代碼 ? SQL
  1. SELECT a.name,a.senior,b.level+1 from menu a, (
  2.   select name,senior,0 level  from menu where senior is null and name='文件'
  3. ) b
  4. where a.senior=b.name
  5.     and a.senior = '文件'
  6.     and a.name='新建'

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

 

第三條記錄:
NAME='使用當前連接查詢',同樣不符合定位點條件,將進入遞歸:

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
    where a.senior = b.name
          AND a.senior = '新建'
          AND a.name = '使用當前連接查詢'

同樣,代入當前記錄條件,下級遞歸b.name='新建',由於'新建'還不符合定位點條件,所以還將繼續遞歸,及lmenu b表示子查詢:

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

替換成上述語句後,d.name='文件',將再次判斷是否需要繼續遞歸,由於'文件'符合終止遞歸條件,所以將終止遞歸。
我們用子查詢表示第三條記錄的遞歸過程如下:

顯示行號 復制代碼 ? SQL
  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 = '使用當前連接查詢'

name                                               senior                                             level
-------------------------------------------------- -------------------------------------------------- -----------
使用當前連接查詢                                           新建                                                 2

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved