一．WITH AS的含義
WITH AS短語，也叫做子查詢部分（subquery factoring），可以定義一個SQL片斷，該SQL片斷會被整個SQL陳述句用到，可以使SQL陳述句的可讀性更高，也可以在UNION ALL的不同部分，作為提供資料的部分，
對于UNION ALL，使用WITH AS定義了一個UNION ALL陳述句，當該片斷被呼叫2次以上，優化器會自動將該WITH AS短語所獲取的資料放入一個Temp表中，而提示meterialize則是強制將WITH AS短語的資料放入一個全域臨時表中，很多查詢通過該方式都可以提高速度，
二．使用方法
先看下面一個嵌套的查詢陳述句：
select * from person.StateProvince where CountryRegionCode in (select CountryRegionCode from person.CountryRegion where Name like 'C%')
上面的查詢陳述句使用了一個子查詢，雖然這條SQL陳述句并不復雜，但如果嵌套的層次過多，會使SQL陳述句非常難以閱讀和維護，因此，也可以使用表變數的方式來解決這個問題，SQL陳述句如下：
declare @t table(CountryRegionCode nvarchar(3)) insert into @t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like 'C%')select * from person.StateProvince where CountryRegionCode in (select * from @t)
雖然上面的SQL陳述句要比第一種方式更復雜，但卻將子查詢放在了表變數@t中，這樣做將使SQL陳述句更容易維護，但又會帶來另一個問題，就是性能的損失，由于表變數實際上使用了臨時表，從而增加了額外的I/O開銷，因此，表變數的方式并不太適合資料量大且頻繁查詢的情況，為此，在SQL Server 2005中提供了另外一種解決方案，這就是公用表運算式（CTE），使用CTE，可以使SQL陳述句的可維護性，同時，CTE要比表變數的效率高得多，
下面是CTE的語法：
[ WITH <common_table_expression> [ ,n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,n ] ) ] AS ( CTE_query_definition )
現在使用CTE來解決上面的問題，SQL陳述句如下：
with cr as ( select CountryRegionCode from person.CountryRegion where Name like 'C%' )select * from person.StateProvince where CountryRegionCode in (select * from cr)
其中cr是一個公用表運算式，該運算式在使用上與表變數類似，只是SQL Server 2005在處理公用表運算式的方式上有所不同，
在使用CTE時應注意如下幾點：
1. CTE后面必須直接跟使用CTE的SQL陳述句（如select、insert、update等），否則，CTE將失效，如下面的SQL陳述句將無法正常使用CTE：
withcr as( select CountryRegionCode from person.CountryRegion where Name like 'C%')select * from person.CountryRegion -- 應將這條SQL陳述句去掉-- 使用CTE的SQL陳述句應緊跟在相關的CTE后面 --select * from person.StateProvince where CountryRegionCode in (select * from cr)
2. CTE后面也可以跟其他的CTE，但只能使用一個with，多個CTE中間用逗號（,）分隔，如下面的SQL陳述句所示：
withcte1 as( select * from table1 where name like 'abc%'),cte2 as( select * from table2 where id > 20),cte3 as( select * from table3 where price < 100)select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
3. 如果CTE的運算式名稱與某個資料表或視圖重名，則緊跟在該CTE后面的SQL陳述句使用的仍然是CTE，當然，后面的SQL陳述句使用的就是資料表或視圖了，如下面的SQL陳述句所示：
-- table1是一個實際存在的表withtable1 as( select * from persons where age < 30)select * from table1 -- 使用了名為table1的公共表運算式select * from table1 -- 使用了名為table1的資料表
4. CTE 可以參考自身，也可以參考在同一 WITH 子句中預先定義的 CTE，不允許前向參考，
--使用遞回公用表運算式顯示遞回的多個級別WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports ;--使用遞回公用表運算式顯示遞回的兩個級別WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports WHERE EmployeeLevel <= 2 --使用遞回公用表運算式顯示層次串列WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, 1, CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID )SELECT EmployeeID, Name, Title, EmployeeLevelFROM DirectReports ORDER BY Sort--使用 MAXRECURSION 取消一條陳述句--可以使用 MAXRECURSION 來防止不合理的遞回 CTE 進入無限回圈，以下示例特意創建了一個無限回圈，然后使用 MAXRECURSION 提示將遞回級別限制為兩級WITH cte (EmployeeID, ManagerID, Title) as( SELECT EmployeeID, ManagerID, Title FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT cte.EmployeeID, cte.ManagerID, cte.Title FROM cte JOIN HumanResources.Employee AS e ON cte.ManagerID = e.EmployeeID)--Uses MAXRECURSION to limit the recursive levels to 2SELECT EmployeeID, ManagerID, TitleFROM cteOPTION (MAXRECURSION 2)--在更正代碼錯誤之后，就不再需要 MAXRECURSION，以下示例顯示了更正后的代碼WITH cte (EmployeeID, ManagerID, Title)AS( SELECT EmployeeID, ManagerID, Title FROM HumanResources.Employee WHERE ManagerID IS NOT NULL UNION ALL SELECT e.EmployeeID, e.ManagerID, e.Title FROM HumanResources.Employee AS e JOIN cte ON e.ManagerID = cte.EmployeeID)SELECT EmployeeID, ManagerID, TitleFROM cte
5. 不能在 CTE_query_definition 中使用以下子句：
（1）COMPUTE 或 COMPUTE BY
（2）ORDER BY（除非指定了 TOP 子句）
（3）INTO
（4）帶有查詢提示的 OPTION 子句
（5）FOR XML
（6）FOR BROWSE
6. 如果將 CTE 用在屬于批處理的一部分的陳述句中，那么在它之前的陳述句必須以分號結尾，如下面的SQL所示：
declare @s nvarchar(3)set @s = 'C%'; -- 必須加分號witht_tree as( select CountryRegionCode from person.CountryRegion where Name like @s)select * from person.StateProvince where CountryRegionCode in (select * from t_tree)
來源連接：https://www.cnblogs.com/Niko12230/p/5945133.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36975.html
標籤：SQL Server