简介

在书写SQL的过程中经常会需要用到中间过程表或者结果集，一般我们实现中间表供后续查询的方法有：使用子查询 或者 创建视图进行存储。但是使用子查询会使语句变得不易于阅读，不利于SQL代码更加简洁和可读，而视图是作为系统对象存在数据库中，那对于结果集仅仅需要在存储过程或是用户自定义函数中使用一次的时候，使用视图就显得有些奢侈了.

公用表表达式（Common Table Expression)是SQL SERVER 2005版本之后引入的一个特性.CTE可以看作是一个临时的结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.

除此之外，根据微软对CTE好处的描述，可以归结为四点:

• 可以定义递归公用表表达式(CTE)
• 当不需要将结果集作为视图被多个地方引用时，CTE可以使其更加简洁
• GROUP BY语句可以直接作用于子查询所得的标量列
• 可以在一个语句中多次引用公用表表达式(CTE)

公用表表达式(CTE)的定义

公用表达式的定义非常简单，只包含三部分：

WITH Expression_Name [ ( ColumnName [1,...n] ) ]
AS
( CTE query definition )

Expression_Name为公用表表达式的名字（在WITH之后），ColumnName所涉及的列名（可选）CTE ，query definition 为SQL语句(紧跟AS之后)

按照是否递归，可以将公用表（CTE）表达式分为递归公用表表达式和非递归公用表表达式.

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

非递归公用表表达式（CTE）是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE

非递归公用表表达式（CTE）的使用方式和视图以及子查询一致

比如一个简单的非递归公用表表达式:

WITH CTE_Test
AS
(
SELECT *
FROM Student
)

SELECT * FROM CTE_Test

当然，公用表表达式的好处之一是可以在接下来一条语句中多次引用:

WITH CTE_Test
AS
(
SELECT *
FROM Student
)
SELECT * FROM CTE_Test a
inner join  CTE_Test b
on a.ID=b.ID
group by a.ID

前面一直强调“在接下来的一条语句中”，意味着只能接下来一条使用:

WITH CTE_Test
AS
(
SELECT *
FROM Student
)
--语句1，正常运行
SELECT * FROM CTE_Test
--语句2，运行报错CTE_Test无效
SELECT * FROM CTE_Test

由于CTE只能在接下来一条语句中使用，因此，当需要接下来的一条语句中引用多个CTE时，可以定义多个，中间用逗号分隔：

WITH CTE_Test
AS
(
SELECT *
FROM Student
)
CTE_Test2
AS
(
SELECT *
FROM School
)

SELECT * FROM CTE_Test
SELECT * FROM CTE_Test2

递归公用表表达式(CTE)

递归是指一个函数或是过程直接或者间接的调用其自身.递归公用表表达式很像派生表（Derived Tables ），指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是，CTE可以在一次定义多次进行派生递归.

对于递归公用表达式来说，需要在语句中定义：基本语句，递归语句，以及递归终止条件。

下面我们通过实例来说明递归CTE的使用

公司层次结构案例

首先建立测试表

create table #dt_user
(
    UserID int,
    ManagerID int,
    Name Nvarchar(10)
)

insert into #dt_user
select 1,-1,N'Boss'
union all
select 11,1,N'A1'
union all
select 12,1,N'A2'
union all
select 13,1,N'A3'
union all
select 111,11,N'B1'
union all
select 112,11,N'B2'
union all
select 121,12,N'C1'

测试表数据：

+─────────+────────────+───────+
| UserID  | ManagerID  | Name  |
+─────────+────────────+───────+
| 1       | -1         | Boss  |
| 11      | 1          | A1    |
| 12      | 1          | A2    |
| 13      | 1          | A3    |
| 111     | 11         | B1    |
| 112     | 11         | B2    |
| 121     | 12         | C1    |
+─────────+────────────+───────+

现在我们通过CTE找出所有员工的上级姓名

with cte as
(
select UserID,ManagerID,name,name as ManagerName
from #dt_user
where ManagerID=-1

union all

select c.UserID,c.ManagerID,c.Name,p.name as ManagerName
from cte P
inner join #dt_user c
    on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ManagerName
from cte
order by UserID

结果为：

+─────────+────────────+───────+──────────────+
| UserID  | ManagerID  | Name  | ManagerName  |
+─────────+────────────+───────+──────────────+
| 1       | -1         | Boss  | Boss         |
| 11      | 1          | A1    | Boss         |
| 12      | 1          | A2    | Boss         |
| 13      | 1          | A3    | Boss         |
| 111     | 11         | B1    | A1           |
| 112     | 11         | B2    | A1           |
| 121     | 12         | C1    | A2           |
+─────────+────────────+───────+──────────────+

这样我们通过递归从最高级别开始，一步步找出下一级中ManageID等于UserID的信息并返回。
具体步骤：
step1：先查询ManagerID=-1，作为root node，这是递归查询的起始点。
step2：迭代公式是 union all 下面的查询语句。在查询语句中调用中cte，而查询语句就是cte的组成部分，即 “自己调用自己”，这就是递归的真谛所在。
所谓迭代，是指每一次递归都要调用上一次查询的结果集，Union ALL是指每次都把结果集并在一起。
step3-N：迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回null 或达到最大的迭代次数，默认值是32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由Union All 子句定义的，并且只能使用Union ALL。

通过递归CTE使查询变得优雅和简洁.这也是CTE最强大的地方.

地理位置层次案例

首先建立数据源表：

create table #hierarchy
(
ID  int not null primary key,
ParentID int not null,
name nvarchar(100) not null
)

insert into #hierarchy
values(1,0,N'河南省')
,(2,1,N'信阳市'),(3,2,N'淮滨县'),(4,3,N'芦集乡'),(12,3,N'邓湾乡'),(13,3,N'台头乡'),(14,3,N'谷堆乡')
              ,(8,2,N'固始县'),(9,8,N'李店乡')
              ,(10,2,N'息县'),(11,10,N'关店乡')
,(5,1,N'安阳市'),(6,5,N'滑县'),(7,6,N'老庙乡')
,(15,1,N'南阳市'),(16,15,N'方城县')
,(17,1,N'驻马店市'),(18,17,N'正阳县')

表结构为：

| ID | ParentID | name |
|----|----------|------|
| 1  | 0        | 河南省  |
| 19 | 0        | 北京市  |
| 20 | 0        | 江苏省  |
| 21 | 1        | 南京市  |
| 2  | 1        | 信阳市  |
| 5  | 1        | 安阳市  |
| 15 | 1        | 南阳市  |
| 17 | 1        | 驻马店市|
| 10 | 2        |  息县   |
| 8  | 2        | 固始县  |
| 3  | 2        | 淮滨县  |
| 22 | 2        | 正阳县  |
| 4  | 3        | 芦集乡  |
| 12 | 3        | 邓湾乡  |
| 13 | 3        | 台头乡  |
| 14 | 3        | 谷堆乡  |
| 6  | 5        | 滑县   |
| 7  | 6        | 老庙乡  |
| 9  | 8        | 李店乡  |
| 11 | 10       | 关店乡  |
| 16 | 15       | 方城县  |
| 18 | 17       | 正阳县  |

下面我们通过CTE筛选出所有河南省下辖的所有行政区域。

select * 
from #hierarchy
order by ParentID

with cte(Id,ParentID,Name) as
(
select * 
from #hierarchy 
where id=1

union all
select h.* 
from #hierarchy h
inner join cte c on h.ParentID=c.id 
)
select *
from cte
order by ParentID

如果想知道具体的行政区划级别：

with cte(Id,ParentID,Name,Level) as
(
select ID,ParentID,Name,0 as Level
from #hierarchy 
where id=1

union all
select h.ID,h.ParentID,h.Name,c.Level+1 as Level
from #hierarchy h
inner join cte c on h.ParentID=c.id 
--where c.id!=h.ID
)
select *
from cte
order by ParentID

结果为：

| Id | ParentID | Name | Level |
|----|----------|------|-------|
| 1  | 0        | 河南省  | 0     |
| 2  | 1        | 信阳市  | 1     |
| 5  | 1        | 安阳市  | 1     |
| 15 | 1        | 南阳市  | 1     |
| 17 | 1        | 驻马店市 | 1     |
| 21 | 1        | 南京市  | 1     |
| 3  | 2        | 淮滨县  | 2     |
| 8  | 2        | 固始县  | 2     |
| 10 | 2        | 息县   | 2     |
| 22 | 2        | 正阳县  | 2     |
| 4  | 3        | 芦集乡  | 3     |
| 12 | 3        | 邓湾乡  | 3     |
| 13 | 3        | 台头乡  | 3     |
| 14 | 3        | 谷堆乡  | 3     |
| 6  | 5        | 滑县   | 2     |
| 7  | 6        | 老庙乡  | 3     |
| 9  | 8        | 李店乡  | 3     |
| 11 | 10       | 关店乡  | 3     |
| 16 | 15       | 方城县  | 2     |
| 18 | 17       | 正阳县  | 2     |

如果想从下级往上级查找，例如：查找芦集乡所属省，市，县

with cte as
(
select ID,ParentID,name
from #hierarchy
where id=4 --芦集乡的ID

union all
select h.ID,h.ParentID,h.name
from #hierarchy h
inner join cte c on h.id=c.ParentID
)
select ID,ParentID,name
from cte
order by ParentID

结果：

| ID | ParentID | name |
|----|----------|------|
| 1  | 0        | 河南省  |
| 2  | 1        | 信阳市  |
| 3  | 2        | 淮滨县  |
| 4  | 3        | 芦集乡  |

CTE的递归终止条件

递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。

默认的递归查询次数是100，可以使用查询提示option(MAXRECURSION）控制递归的最大次数：OPTION( MAXRECURSION 16)；
如果允许无限制的递归次数，使用查询提示：option(maxrecursion 0)；
当递归查询达到指定或默认的 MAXRECURSION 数量限制时，SQL Server将结束查询并返回错误，如下：

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过 where 条件限制递归的次数。

总结

CTE是一种十分优雅的存在。CTE所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。

个人公众号：Smilecoc的杂货铺，欢迎关注！