公用表表达式 (CTE)、递归

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

对于递归公用表达式来说，实现原理也是相同的，同样需要在语句中定义两部分：

•    基本语句
•    递归语句

   在SQL这两部分通过UNION ALL连接结果集进行返回：

with cte as
(
    select Id,Pid,DeptName,0 as lvl from Department
    where Id = 2
    union all
    select d.Id,d.Pid,d.DeptName,lvl+1 from cte c inner join Department d
    on c.Id = d.Pid
)
select * from cte

 

 

使用CTE准则

创建CTE时，需要注意的一些准则，详见MSND列出的使用准则：

1. 定义CTE时最好加前缀”;”
2. CTE内部定义的列字段要保持一致
3. CTE with之后第一句必须使用CTE的select。即CTE的生命周期只是在第一次使用之后就消亡。
4. sp中只能使用一次with语句。
5. 定义多个CTE时，只声明一个with关键字就行，比如

with test1

as

(

select * …………

)，

test2 as

(

select * …………

)

通用表表达式(CTE)是SQL Server的一项新功能。本质上CTE是一个临时结果集，它仅仅存在于它发生的语句中。可以在SELECT、INSERT、DELETE或CREATE VIEW语句中建立一个CTE。

举例，新建一个家庭关系表，以递归的方式存储

关系图：

表：

查询：查询出爸爸和爸爸的子孙

with CTE as(
select id,parentId,name from family where name='爸爸'
union all 
select family.id,family.parentId,family.name from family 
 join CTE  on  family.parentId=CTE.id
) 
select * from CTE

查询结果如下：