T-SQL:CTE用法（十）

CTE 也叫公用表表達式和派生表非常類似 先定義一個USACusts的CTE  

WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts;

with  ()  稱為內部查詢   與派生表相同，一旦外部查詢完成後，CTE就自動釋放了

CTE內部方式 就是上面代碼所表示的方式  其實還有一種外部方式

WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

C(orderyear, custid)  可以理解為 select orderyear, custid from C   指定返回你想要的列  不過個人感覺沒什麼用！

它和派生表相同 也可以在CTE中查詢使用參數

DECLARE @empid AS INT = 3;

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
  WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

定義多個CTE

WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

多個CTE用 , 隔開 通過with 記憶體 可以在外查詢中多次引用

WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear, 
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

可以需要在多個相同表結果做物理實例化  這樣可以節省很多查詢時間 或者在臨時表和表變數中固化內部查詢結果

遞歸CTE

遞歸CTE至少由兩個查詢定義，至少一個查詢作為定位點成員，一個查詢作為遞歸成員。

遞歸成員是一個引用CTE名稱的查詢 ，在第一次調用遞歸成員，上一個結果集是由上一次遞歸成員調用返回的。 其實就和C# 方法寫遞歸一樣  返回上一個結果集 依次輸出

   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                UNION ALL  
                SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
             )
    SELECT *
     FROM Emp 

在前面也寫過 sql 語句的執行順序 其實到  FROM Emp   時 就進行了節點第一次遞歸  當我們遞歸到第三次的時候 這個為執行的sql 語句實際是什麼樣的呢

   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=3
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=4
                UNION ALL  
                SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
             )
    SELECT *
     FROM Emp 

簡單理解可以把它看成兩部分

SELECT  * FROM  dbo.dt_users
               WHERE  id=2

   SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id

上部分的結果集 會儲存成最後顯示的結果 下部分的結果集  就是下一次遞歸的 上部分結果集 依次拼接  就是這個遞歸最後的結果集 

下部分 在詳解  認真看很有意思

  SELECT d.* FROM  Emp

SELECT d.* FROM   dbo.dt_users d

from Emp 源數據來自  d  在 on  d.agent_id = Emp.id 就是自連接 而 Emp.id 結果 來自哪裡呢  就是上部分結果集 如果是第一次運行結果集就是上部分運行的結果   記住下部分操作結果集都是當前的上部分結果集。

預設情況下遞歸是100次 也可在 外部查詢 指定遞歸次數 MAXRECURSION N 0~32767 次範圍 MAXRECURSION 0 並不是0次實際上是遞歸次數無限制