sql-公用表表达式（CTE）

定义

公用表表达式（CTE），是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次（但在其作用域内可以被引用任意次），并且在该查询生存期间将一直生存。可以使用CTE来执行递归操作。创建的语法是：

with <name of you cte>(<column names>)

as(

<actual query>

)

select * from <name of your cte>

使用公共表表达式的好处：

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

非递归公用表表达式

非递归公用表表达式是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE。非递归公用表表达式的使用方式和视图以及子查询一致。

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

 

递归公用表表达式

递归公用表表达式指的是在CTE内的语句中调用其自身的CTE。

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

•    基本语句
•    递归语句

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

先建一张表栏目表如下，栏目Id，栏目名称，栏目的父栏目。

　　　

现在使用CTE查询其每个栏目是第几层栏目的代码如下：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 　　--递归调用
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE

 

输出结果如下：

　　

　　0表示顶级栏目。1就是1级栏目。语法非常优雅。就一个SELECT * FRON COL_CTE。这正是CTE强大的地方，但是，这要有约束，否则如果无限制递归可以会消耗掉非常多的系统资源。还可以通过OPTION(MAXRECURSION n)　限制递归的最大次数。

　如将上面的查询语法改为：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE
OPTION(MAXRECURSION 2)　　--指定最大递归次数为2

 

使用时注意点

with开始前有时候建议加';'符号结束前面的语句。因为with在其他地方有他的含义，加以区别。

总结 

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