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次實際上是遞迴次數無限制