共計 7308 個字符，預計需要花費 19 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章給大家介紹 with as 怎么在 SQL Server 中使用，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
一．WITH AS 的含義
WITH AS 短語，也叫做子查詢部分（subquery factoring），可以讓你做很多事情，定義一個 SQL 片斷，該 SQL 片斷會被整個 SQL 語句所用到。有的時候，是為了讓 SQL 語句的可讀性更高些，也有可能是在 UNION ALL 的不同部分，作為提供數據的部分。
特別對于 UNION ALL 比較有用。因為 UNION ALL 的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，所以可以使用 WITH AS 短語，則只要執行一遍即可。如果 WITH AS 短語所定義的表名被調用兩次以上，則優化器會自動將 WITH AS 短語所獲取的數據放入一個 TEMP 表里，如果只是被調用一次，則不會。而提示 materialize 則是強制將 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：
with
cr 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 語句所示：
with
cte1 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.id3. 如果 CTE 的表達式名稱與某個數據表或視圖重名，則緊跟在該 CTE 后面的 SQL 語句使用的仍然是 CTE，當然，后面的 SQL 語句使用的就是數據表或視圖了，如下面的 SQL 語句所示：
— table1 是一個實際存在的表
with
table1 as
(
select * from persons where age 30
)
select * from table1 — 使用了名為 table1 的公共表表達式
select * from table1 — 使用了名為 table1 的數據表
4. CTE 可以引用自身，也可以引用在同一 WITH 子句中預先定義的 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%
; -- 必須加分號
t_tree as
select CountryRegionCode from person.CountryRegion where Name like @s
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)CTE 除了可以簡化嵌套 SQL 語句外，還可以進行遞歸調用，關于這一部分的內容將在下一篇文章中介紹。
先看如下一個數據表（t_tree）：
上圖顯示了一個表中的數據，這個表有三個字段：id、node_name、parent_id。實際上，這個表中保存了一個樹型結構，分三層：省、市、區。其中 id 表示當前省、市或區的 id 號、node_name 表示名稱、parent_id 表示節點的父節點的 id。
現在有一個需求，要查詢出某個省下面的所有市和區（查詢結果包含省）。如果只使用 SQL 語句來實現，需要使用到游標、臨時表等技術。但在 SQL Server2005 中還可以使用 CTE 來實現。
從這個需求來看屬于遞歸調用，也就是說先查出滿足調價的省的記錄，在本例子中的要查“遼寧省”的記錄，如下：
id node_name parent_id
1 遼寧省 0
然后再查所有 parent_id 字段值為 1 的記錄，如下：
id node_name parent_id
2 沈陽市 1
3 大連市 1
最后再查 parent_id 字段值為 2 或 3 的記錄，如下：
id node_name parent_id
4 大東區 2
5 沈河區 2
6 鐵西區 2
將上面三個結果集合并起來就是最終結果集。
上述的查詢過程也可以按遞歸的過程進行理解，即先查指定的省的記錄（遼寧省），得到這條記錄后，就有了相應的 id 值，然后就進入了的遞歸過程，如下圖所示。
從上面可以看出，遞歸的過程就是使用 union all 合并查詢結果集的過程，也就是相當于下面的遞歸公式：
resultset(n) = resultset(n-1) union all current_resultset
其中 resultset(n) 表示最終的結果集，resultset(n – 1) 表示倒數第二個結果集，current_resultset 表示當前查出來的結果集，而最開始查詢出“遼寧省”的記錄集相當于遞歸的初始條件。而遞歸的結束條件是 current_resultset 為空。下面是這個遞歸過程的偽代碼：
public resultset getResultSet(resultset)
if(resultset is null)
{
current_resultset = 第一個結果集（包含省的記錄集） 將結果集的 id 保存在集合中
getResultSet(current_resultset)
}
current_resultset = 根據 id 集合中的 id 值查出當前結果集
if(current_result is null) return resultset
將當前結果集的 id 保存在集合中
return getResultSet(resultset union all current_resultset)
// 獲得最終結果集
resultset = getResultSet(null) 從上面的過程可以看出，這一遞歸過程實現起來比較復雜，然而 CTE 為我們提供了簡單的語法來簡化這一過程。
實現遞歸的 CTE 語法如下：
[WITH common_table_expression [ ,n] ]
common_table_expression ::=
expression_name [( column_name [ ,n] ) ]
AS (
CTE_query_definition1 — 定位點成員（也就是初始值或第一個結果集）
union all
CTE_query_definition2 — 遞歸成員
)
sql 語句
with
district as
-- 獲得第一個結果集，并更新最終結果集
select * from t_tree where node_name= N 遼寧省
union all
-- 下面的 select 語句首先會根據從上一個查詢結果集中獲得的 id 值來查詢 parent_id
-- 字段的值，然后 district 就會變當前的查詢結果集，并繼續執行下面的 select 語句
-- 如果結果集不為 null，則與最終的查詢結果合并，同時用合并的結果更新最終的查
-- 詢結果；否則停止執行。最后 district 的結果集就是最終結果集。 select a.* from t_tree a, district b
where a.parent_id = b.id
select * from district
district as
select * from t_tree where node_name= N 遼寧省
union all
select a.* from t_tree a, district b
where a.parent_id = b.id
district1 as
select a.* from district a where a.id in (select parent_id from district)
select * from district1注：只有“遼寧省”和“沈陽市”有下子節點。
在定義和使用遞歸 CTE 時應注意如下幾點：
1. 遞歸 CTE 定義至少必須包含兩個 CTE 查詢定義，一個定位點成員和一個遞歸成員。可以定義多個定位點成員和遞歸成員；但必須將所有定位點成員查詢定義置于第一個遞歸成員定義之前。所有 CTE 查詢定義都是定位點成員，但它們引用 CTE 本身時除外。
2. 定位點成員必須與以下集合運算符之一結合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后一個定位點成員和第一個遞歸成員之間，以及組合多個遞歸成員時，只能使用 UNION ALL 集合運算符。
3. 定位點成員和遞歸成員中的列數必須一致。
4. 遞歸成員中列的數據類型必須與定位點成員中相應列的數據類型一致。
5. 遞歸成員的 FROM 子句只能引用一次 CTE expression_name。
6. 在遞歸成員的 CTE_query_definition 中不允許出現下列項：
（1）SELECT DISTINCT
（2）GROUP BY
（3）HAVING
（4）標量聚合
（5）TOP
（6）LEFT、RIGHT、OUTER JOIN（允許出現 INNER JOIN）
（7）子查詢
（8）應用于對 CTE_query_definition 中的 CTE 的遞歸引用的提示。
7. 無論參與的 SELECT 語句返回的列的為空性如何，遞歸 CTE 返回的全部列都可以為空。
8. 如果遞歸 CTE 組合不正確，可能會導致無限循環。例如，如果遞歸成員查詢定義對父列和子列返回相同的值，則會造成無限循環。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中的一個 0 到 32,767 之間的值，來限制特定語句所允許的遞歸級數，以防止出現無限循環。這樣就能夠在解決產生循環的代碼問題之前控制語句的執行。服務器范圍內的默認值是 100。如果指定 0，則沒有限制。每一個語句只能指定一個 MAXRECURSION 值。
9. 不能使用包含遞歸公用表表達式的視圖來更新數據。
10. 可以使用 CTE 在查詢上定義游標。遞歸 CTE 只允許使用快速只進游標和靜態（快照）游標。如果在遞歸 CTE 中指定了其他游標類型，則該類型將轉換為靜態游標類型。
11. 可以在 CTE 中引用遠程服務器中的表。如果在 CTE 的遞歸成員中引用了遠程服務器，那么將為每個遠程表創建一個假脫機，這樣就可以在本地反復訪問這些表。
下面是一些補充，很多參考價值
WITH AS 短語，也叫做子查詢部分（subquery factoring）
可以讓你做很多事情，定義一個 SQL 片斷，該 SQL 片斷會被整個 SQL 語句所用到。
作為提供數據的部分。
代碼例子：
with temp as
(select ID, Type_Name, Type_ID
from T_Base_GoodsType as t
where t.Shop_ID = @shop_id
and Type_ID = @Goods_TypeID
union all
select t1.ID, t1.Type_Name, t1.Type_ID
from T_Base_GoodsType as t1
inner join temp
on t1.ParentType_ID = temp.Type_ID
where t1.Shop_ID = @shop_id)
select *
from (select Stock_Amount,
S.StockWarn_Amount,
S.All_Amount,
G.Goods_ID,
G.Goods_Name,
G.Goods_Unit,
ROW_NUMBER() over(order by Stock_Amount desc) as rowid
from T_IM_StockInfo as S
inner join T_Base_GoodsInfo AS G
on S.Goods_ID = G.Goods_ID
inner join temp
on temp.Type_ID = G.Goods_TypeID
where S.Shop_ID = @shop_id
AND G.Shop_ID = @shop_id
and G.Goods_TypeID = temp.Type_ID
group by S.Stock_Amount,
S.All_Amount,
G.Goods_ID,
G.Goods_Name,
G.Goods_Unit,
S.StockWarn_Amount
HAVING SUM(S.Stock_Amount) S.StockWarn_Amount) m
WHERE rowid between @pageindex and @pagesizesql 循環（WITH AS 短語也叫做子查詢部分）
-- 表結構 SELECT id,position,Parentid FROM op_client_sales_structure
WITH TEST_CTE
AS
( SELECT id,position,Parentid,Cast(Parentid AS NVARCHAR(4000)) AS PATH
FROM op_client_sales_structure team
WHERE Parentid !=-1
UNION ALL
SELECT a.id,a.position,a.Parentid,
CTE.PATH+ , +Cast(a.Parentid AS NVARCHAR(4000)) AS PATH
FROM op_client_sales_structure a
INNER JOIN TEST_CTE CTE ON a.id=CTE.Parentid
SELECT * FROM TEST_CTE WHERE Parentid=(SELECT id FROM op_client_sales_structure WHERE Parentid=-1)
-- 限制遞歸次數
OPTION(MAXRECURSION 10)關于 with as 怎么在 SQL Server 中使用就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
向 AI 問一下細節
