公用表表达式 Common Table Expression

What  -CTE是什么

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

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

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

 

HOW -基本写法

WITH expression_name[(column_name [,...])]
AS
    (CTE_definition)

SQL_statement;

 

定义多个CTE写法

WITH cte_1[(column_name [,...])]
AS
    (cte_1_definition)，

cte_2

    (cte_2_definition)，

cte_2

    (cte_3_definition)，

SQL_statement;

语法：

1、公用表表达式的名字（在WITH之后），提供后续调用，有且只能调用一次

2、所涉及的列名（可选）

3、 一个SELECT语句(紧跟AS之后)

4、With之前如果有SQL语句，需要用分号隔开（；）

 

非递归用法

目的：可以在接下来一条语句中多次引用:

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

with cte
AS(SELECT * FROM T_SomeTable)

select * from cte a inner join cte b
on a.SomeColumn= b.SomeColumn

 

递归用法

（基本语句、递归语句）

With recursive_CTE  

AS (  

SELECT p1.* FROM T_SomeTable p1      

where p1.SomeColumn = 'XXXXX'                   --递归初始条码                  

UNION ALL  

SELECT p2.* FROM T_SomeTable p2  

INNER JOIN recursive_CTE   

on recursive_CTE.SomeColumn = p2.SomeColumn    -- 递归条码

)  

Select * From recursive_CTE                        --执行表达式，获取值

限制递归次数

Select * From recursive_CTE
OPTION ( MAXRECURSION 2)            — 限制最大递归次数为2

 

WHY -- 

             提升代码整洁度，轻松实现查询语句的递归

 

 

参考

https://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html