转：CTE(公共表表达式)——WITH子句

来自：《Microsoft SQL Server 2008技术内幕：T-SQL语言基础》

一、公共表表达式（CTE，Common Table Expression）是在SQL Server 2005中引入的，是ANSI SQL标准的一部分。

CTE是用WITH定义的，它的一般格式为：

WITH <CTE_名称>[(目标列_列表)]　　　　　　　　　　　　　　WITH <CTE_Name>[(target_column_list)]

AS　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 AS

( 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（

　　<定义CTE的内部查询>　　　　　　　　　　　　　　　　　　　　　　<innser_query_defining_CTE>

)　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ）

<对CTE进行查询的外部查询>;　　　　　　　　　　　　　　　　　<outer_query_against_CTE>;　　

如：

with XiaoMingInfo
as
(
    select * from Student where Name='小明'
)
select * from XiaoMingInfo;

注：(来自：http://wudataoge.blog.163.com/blog/static/80073886200961652022389/)

1、如果CTE的名称(<CTE_Name>)与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，在这之后的SQL语句使用的则是数据表或视图了。如：

select Id,Name,BirthdayYear from XiaoMingInfo;

with Person　　--是一个实际存在的表
as
(
    select * from Student where Name='小明'
)
select * from Person    --使用的是名为Person的CTE
select * from Person    --使用的是名为Person的数据表

2、不能在<innser_query_defining_CTE>中使用以下语句：
　　(1)、COMPUTE或COMPUTE BY

　　(2)、ORDER BY(除非指定了TOP子句)

　　(3)、INTO

　　(4)、带有查询提示的 OPTION 子句

　　(5)、FOR XML

　　(6)、FOR BROWSE

二、分配列别名：
1、在内部使用as关键字

with XiaoMingInfo
as
(
    select Id,Name,year(Birthday) as BirthdayYear from Student where Name='小明'
)
select Id,Name,BirthdayYear from XiaoMingInfo;

2、在CTE名称后面的一对圆括号中指定目标的列表

with XiaoMingInfo(Id,Name,BirthdayYear)
as
(
    select Id,Name,year(Birthday) from Student where Name='小明'
)
select Id,Name,BirthdayYear from XiaoMingInfo;

三、使用参数

declare @stuName nvarchar(20)
set @stuName = '小明';    --CTE前一个语句必须以分号结尾
with XiaoMingInfo(Id,Name,BirthdayYear)
as
(
    select Id,Name,year(Birthday) from Student where Name=@stuName
)
select Id,Name,BirthdayYear from XiaoMingInfo;

四、定义多个CTE

with PersonInfo
as
(
    select * from Student where year(Birthday)=year(getdate())
),
XiaoMingInfo
as
(
    select * from PersonInfo where Name='小明'
)
select * from XiaoMingInfo

五、递归CTE：CTE支持递归查询。定义一个递归CTE至少需要两个查询；第一个查询称为定位点成员，第二个查询称为递归成员。递归CTE的基本格式如下：

WITH <CTE_Name>[(target_column_list)]

AS

( 　　

　　<anchor_member>

　　UNION ALL

　　<recursive_member>

)

<outer_query_against_CTE>;

定位点成员只是一个返回有效关系结果表的查询，与用于定义非递归表表达式的查询类似。定位点成员只被调用一次。递归成员是一个引用了CTE名称的查询。对CTE名称的引用代表的是在一个执行序列中逻辑上的“前一个结果集”。第一次调用递归成员时，“前一个结果集”代表有定位点成员返回的任何结果集。之后每次调用递归成员时，对CTE名称的引用代表对递归成员的前一次调用所返回的结果集。递归成员没有显式的递归终止检查(终止检查是隐式的)。递归成员会一直被重复调用，直到返回空结果集或超过某种限制条件。

在查询返回的结果上，两个成员查询必须在列的个数和相应列的数据类型上保持兼容。

外部查询中的CTE名称引用代表对定位点成员调用和所有对递归成员调用的联合结果集。

1、不将自己包括在内

 

2、将自己包括在内