熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

SQL Server 2005 Beta 2 Transact-SQL功能(2)

2013-11-15 14:32:59  來源: SQL Server 

  單父節點環境雇員組織結構圖
  
  對於單父節點層次結構方案使用雇員組織結構圖
  
  注 本節中的示例使用一個名為 Employees 的表該表具有與 AdventureWorks 中的 HumanResourcesEmployee 表不同的結構您應當在自己的測試數據庫或 tempdb 中運行代碼而不要在 AdventureWorks 中運行代碼
  
  以下代碼生成 Employees 表並且用示例數據填充它
  
  USE tempdb or your own test database
  CREATE TABLE Employees
  (
   empid  int     NOT NULL
   mgrid  int     NULL
   empname varchar() NOT NULL
   salary money    NOT NULL
   CONSTRAINT PK_Employees PRIMARY KEY(empid)
   CONSTRAINT FK_Employees_mgrid_empid
  FOREIGN KEY(mgrid)
  REFERENCES Employees(empid)
  )
  CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
  SET NOCOUNT ON
  INSERT INTO Employees VALUES( NULL Nancy  $)
  INSERT INTO Employees VALUES(   Andrew  $)
  INSERT INTO Employees VALUES(   Janet  $)
  INSERT INTO Employees VALUES(   Margaret $)
  INSERT INTO Employees VALUES(   Steven  $)
  INSERT INTO Employees VALUES(   Michael $)
  INSERT INTO Employees VALUES(   Robert  $)
  INSERT INTO Employees VALUES(   Laura  $)
  INSERT INTO Employees VALUES(   Ann   $)
  INSERT INTO Employees VALUES(   Ina   $)
  INSERT INTO Employees VALUES(   David  $)
  INSERT INTO Employees VALUES(   Ron   $)
  INSERT INTO Employees VALUES(   Dan   $)
  INSERT INTO Employees VALUES(   James  $)
  
  每個雇員都向其 ID 存儲在 mgrid 列中的經理匯報在引用 empid 列的 mgrid 列上定義了一個外鍵這意味著經理 ID 必須對應於該表中的一個有效雇員 ID 或者為 NULL老板 Nancy 在 mgrid 列中具有 NULL 值經理雇員關系如圖 所示
   
  圖 雇員組織結構圖
  
  下面是一些可能在 Employees 表上運行的常見請求
  
  顯示有關 Robert (empid=) 及其所有級別下屬的詳細信息
  
  顯示有關比 Janet (empid=) 低兩個級別的所有雇員的詳細信息
  
  顯示通向 James (empid=) 的管理鏈
  
  顯示有多少個雇員直接或間接向每個經理匯報
  
  以適當的方式顯示所有雇員以便可以容易地查看他們的層次依賴項
  
  遞歸 CTE 提供了處理上述請求(它們在本質上是遞歸的)的手段而無須在數據庫中維護有關層次結構的其他信息
  
  第一個請求可能是最常見的一個請求返回某個雇員(例如empid= 的 Robert)及其所有級別的下屬以下 CTE 提供了對該請求的解決方案
  
  WITH EmpCTE(empid empname mgrid lvl)
  AS
  (
  
    Anchor Member (AM)
   SELECT empid empname mgrid
   FROM Employees
   WHERE empid =
   UNION ALL
  
    Recursive Member (RM)
   SELECT Eempid Eempname Emgrid Mlvl+
   FROM Employees AS E
  JOIN EmpCTE AS M
   ON Emgrid = Mempid
  )
  SELECT * FROM EmpCTE
  
  以下為結果集
  
  empid    empname          mgrid    lvl
  
        Robert               
       David                
       Ron                 
       Dan                 
       James                
  
  按照先前描述的遞歸 CTE 邏輯該 CTE 被按如下方式處理
  
  錨定成員被激活並且從 Employees 表中返回 Robert 的行請注意在 lvl 結果列中返回的常量
  
   遞歸成員被反復激活並且借助於 Employees 和 EmpCTE 之間的聯接操作返回上一個結果的直接下屬Employees 代表下屬而 EmpCTE(它包含上一個調用的結果)代表經理
  
  首先Robert 的下屬被返回DavidRon 和 James
  
  然後DavidRon 和 Dan 的下屬被返回只有 James
  
  最後James 的下屬被返回無 — 在這種情況下遞歸終止
  
   外部查詢從 EmpCTE 中返回所有行
  
  請注意對於每個遞歸調用lvl 值反復遞增
  
  使用該級別計數器您可以限制遞歸中的迭代次數例如以下 CTE 用來返回比 Janet 低兩個級別的所有雇員
  
  WITH EmpCTEJanet(empid empname mgrid lvl)
  AS
  (
   SELECT empid empname mgrid
   FROM Employees
   WHERE empid =
   UNION ALL
  
   SELECT Eempid Eempname Emgrid Mlvl+
   FROM Employees as E
  JOIN EmpCTEJanet as M
   ON Emgrid = Mempid
   WHERE lvl <
  )
  SELECT empid empname
  FROM EmpCTEJanet
  WHERE lvl =
  
  以下為結果集
  
  empid    empname
  
       David
       Ron
       Dan
  
  該代碼示例中比上一個代碼示例增加的代碼以粗體顯示遞歸成員中的篩選器 WHERE lvl < 被用作遞歸終止檢查 — 當 lvl = 不會返回任何行因而遞歸停止外部查詢中的篩選器 WHERE lvl = 用來移除上至級別 的所有級別請注意從邏輯上講外部查詢中的篩選器 (lvl = ) 本身就足以只返回所需的行遞歸成員中的篩選器 (lvl < ) 是出於性能原因而添加的 — 為了在返回 Janet 下的兩個級別之後立即停止遞歸
  
  正如前面提到的那樣CTE 可以引用在同一批處理中定義的本地變量例如為了使查詢更一般化您可以使用變量而不是常量作為雇員 ID 和級別
  
  DECLARE @empid AS INT @lvl AS INT
  SET @empid = Janet
  SET @lvl  = two levels
  WITH EmpCTE(empid empname mgrid lvl)
  AS
  (
   SELECT empid empname mgrid
   FROM Employees
   WHERE empid = @empid
   UNION ALL
  
   SELECT Eempid Eempname Emgrid Mlvl+
   FROM Employees as E
  JOIN EmpCTE as M
   ON Emgrid = Mempid
   WHERE lvl < @lvl
  )
  SELECT empid empname
  FROM EmpCTE
  WHERE lvl = @lvl
  
  您可以使用提示在已經調用一定數量的遞歸迭代之後強行終止查詢可以通過在外部查詢的結尾添加 OPTION(MAXRECURSION value) 做到這一點如以下示例所示
  
  WITH EmpCTE(empid empname mgrid lvl)
  AS
  (
   SELECT empid empname mgrid
   FROM Employees
   WHERE empid =
   UNION ALL
  
   SELECT Eempid Eempname Emgrid Mlvl+
   FROM Employees as E
  JOIN EmpCTE as M
   ON Emgrid = Mempid
  )
  SELECT * FROM EmpCTE
  OPTION (MAXRECURSION )
  
  以下為結果集
  
  empid    empname          mgrid    lvl
  
        Nancy           NULL    
        Andrew               
        Janet                
        Margaret              
       Ina                 
        Robert               
        Laura                
        Ann                 
  Net SqlClient Data Provider: Msg Level State Line
  Statement terminated Maximum recursion has been exhausted before statement completion
  
  可能返回(但是不能保證返回)迄今生成的結果並且生成錯誤 您可能會想到使用 MAXRECURSION 選項實現以下請求使用 MAXRECURSION 提示而不是遞歸成員中的篩選器返回 Janet 下兩個級別的雇員
  
  WITH EmpCTE(empid empname mgrid lvl)
  AS
  (
   SELECT empid empname mgrid
   FROM Employees
   WHERE empid =
   UNION
From:http://tw.wingwit.com/Article/program/SQLServer/201311/21971.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.