with as短语，也叫做子查询部分(subquery factoring)。
如果with as所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据存入一个TEMP表里。如果只被调用一次，则不会。
而提示materrialize则是强制将with as短语获取的数据存入一个全局临时表里。很多查询通过这种方式可以提高速度。
CTE语法如下：
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
使用CTE需要注意：
1、CTE后面必须直接跟使用CTE的SQL语句，否则，CTE将失效。
2、CTE后面也可以跟其它的CTE，但只能使用一个with，多个CTE之间使用逗号（,）分隔。
with
p1 as(
select * from dbo.Person where LastName like '%a%'
),
p2 as(
select * from dbo.Person where Address like '%e%'
)
select a.* from p1 a join p2 b on a.PID=b.PID
3、如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了。
4、CTE递归调用：
--if(exists(select * from sys.tables where name='t_tree'))
--drop table t_tree
--go
--create table t_tree(
-- id int not null,
-- node_name varchar(50) null,
-- parent_id int not null
--)
--go
--insert into dbo.t_tree(id,node_name,parent_id)
--values(1,'广东省',0),(2,'广州市',1),(3,'惠州市',1),(4,'白云区',2),(5,'天河区',2),(6,'越秀区',2),(7,'黄埔区',2),(8,'龙门县',3)
select * from dbo.t_tree
--使用CTE递归查询某省下所有区域--使用 MAXRECURSION 来防止不合理的递归
;with district(id,node_name,parent_id,level) as(
select *,0 as level from dbo.t_tree where node_name='广东省' --定点子查询
union all
select a.*,b.level+1 from dbo.t_tree a join district b
on a.parent_id=b.id--递归子查询
)
select * from district
option(maxrecursion 5)---- 限制特定语句所允许的递归级数，以防止出现无限循环
CTE递归查询终止条件：
递归查询没有显式的终止条件，只有当递归子查询返回的是空结果集（没有数据行返回），或是超出递归次数最大限制时，才停止递归。
默认的递归查询次数是100.可用option(maxrecursion N)来控制递归最大次数。
参数文献：
/johnf_nash/article/details/78681060
/tougao/article/5656.html#
