共计 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 问一下细节
