sql 性能优化：SQL Server with as 的作用提高重复sql语句执行效率；这个语法递归很实用呀,分层子查询也很棒呢

本文详细介绍了SQL Server中WITH AS子句的作用，特别是其在性能优化和递归查询方面的应用。WITH AS用于定义公共表表达式(CTE)，能提高重复SQL语句的执行效率，避免多次执行相同查询。同时，文章通过实例展示了如何使用CTE进行递归查询，如查询树形结构数据，强调了递归CTE的使用注意事项，以防止无限循环，并讨论了MAXRECURSION选项的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

注意：虽然本文是转载，但是内容里面，作者加了一些便于阅读的“注释”，“数据表”等

一．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短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

--------------------
/*番外篇 UNION*/
--------------------
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。


【SQL UNION 语法】

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。


【SQL UNION ALL 语法】

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。


下面的例子中使用的原始表：
[表Employees_China:]
E_ID	E_Name
01	    Zhang, Hua
02	    Wang, Wei
03	    Carter, Thomas
04	    Yang, Ming

[表Employees_USA]
E_ID	E_Name
01	    Adams, John
02	    Bush, George
03	    Carter, Thomas
04	    Gates, Bill


【使用 UNION 命令】

实例
列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。


UNION ALL
UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。


【使用 UNION ALL 命令】
实例：
列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill


摘录：https://www.w3school.com.cn/sql/sql_union.asp

二．使用方法

表：person.StateProvince（人国家省）
字段名	类型	说明
CountryRegionCode	---	国家地区代码
---	---	---
---	---	---

表：person.CountryRegion（国家地区）
字段名	类型	说明
Name	---	名称
---	---	---
---	---	---

先看下面一个嵌套的查询语句：

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) 

/*
代码解读 
declare @t table(CountryRegionCode nvarchar(3)) 
声明一个变量“t”，类型是：table。
表字段是：CountryRegionCode ，类型是：nvarchar(3)
*/

虽然上面的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.id

3. 如果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%' 
; -- 必须加分号 
with 
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）：

表：t_tree（国家地区）
字段名	类型	说明
id	int	id
node_name	nvarchar	名称
parent_id	int	父id

上图显示了一个表中的数据，这个表有三个字段：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值，然后就进入了的递归过程，如下图所示。

上述示例准备sql 如下：

if exists (
       select *
       from   sys.tables 
              where name = 't_tree'
   )
    drop table dbo.t_tree;
go
 
create table t_tree
(
   id                int not null
   ,node_name         varchar(50) null
   ,parent_id         int not null
   ,[description]     varchar(255) null
)
go
 
insert into t_tree
  (
    id
   ,node_name
   ,parent_id
  )
values
  (
    1
   ,'辽宁省'
   ,0
  ),
(2 ,'沈阳市' ,1),
(3 ,'大连市' ,1),
(4 ,'大东区' ,2),
(5 ,'沈河区' ,2),
(6 ,'铁西区' ,2)
go

从上面可以看出，递归的过程就是使用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  --   递归成员 
     )

CTE 使用示例

1. 查询节点及其下所有子节点

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

2. 查询有子节点的节点

with 
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

注：只有“辽宁省”和“沈阳市”有下子节点。

3.使用 MAXRECURSION 来防止不合理的递归

;with district as (
	 select * from t_tree where node_name= N'辽宁省' 
	 union all
      select a.* from t_tree a, district b
	  where a.id = b.id      -- 故意制造无限循环
)
select * from district
OPTION (MAXRECURSION 5)  -- 限制特定语句所允许的递归级数，以防止出现无限循环

4. with as 与 insert 结合使用

if exists (
       select *
       from   sys.objects
              where name = 'fn_Test'
       and type in ('FN' ,'TF')
   )
    drop function dbo.fn_Test;
go
 
create function dbo.fn_Test
(
	@nodename varchar(50)
)
returns @result table (
            id int not null
           ,node_name varchar(50) null
           ,parent_id int not null
           ,[description] varchar(255) null
        )
as
begin
	;with district as (
				 select *
				 from   t_tree where node_name = @nodename
	         
				 union all
 
				select a.* 
				from   t_tree a, district b
					   where a.parent_id = b.id
	),
	district1 as ( 
		select *
		from   district a 
				where a.id in (select parent_id
								from   district)
	)
	
	insert into @result
	  (
	    id
	   ,node_name
	   ,parent_id
	   ,[description]
	  )
	select id
	      ,node_name
	      ,parent_id
	      ,[description]
	from   district1;
	
	return ;
end;
go

四、递归 CTE 使用注意事项

在定义和使用递归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 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

本文转自SQL Server with as使用_我飞故我在的专栏-优快云博客_sqlserver中with的用法