sqlserver 公有表达式

了解通用表达式：

为了让代码简洁：在一个查询中引用另外的结果集都是通过视图而不是子查询来进行分解，但是视图是系统级对象，如果数据集仅仅需要在存储过程或是自定义函数中使用一次的话，使用view有些奢侈哈

所以可以考虑用公用表达式：
可以看做是一个临时的结果集，可以在select ，insert，update，delete，merge语句中多次被引用

CTE：公用表达式
优点：group by语句可以直接作用于子查询所得的标量列
可以在一个语句中多次引用公用表达式

定义：
1公用表达式的名字在 with之后
2 一个select语句(在as之后)
3 所涉及的列名
WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

 

按照是否递归：可以分为递归公用表达式和非递归公用表达式

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

例如：

WITH CTE_test
AS
(
    SELECT * 
    FROM dbo.Customer AS c
)


SELECT * FROM CTE_test

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

意思是只能在一条语句中多次引用，但是如果在不同的语句中则不能调用

但是如果需要在下面语句引用它，则需要重新定义它，如果需要定义多个，中间可以用逗号分隔

递归公用表达式：指得是在CTE内的语句调用其自身的CTE，CTE可以一次定义多次进行派生递归，

递归公有表达式：
需要实现两部分：
1 基本语句
2 递归语句
在sql中这两部分通过Union all连接结果集进行返回

递归遵循的法则：
sqlserer 提供了Option选项，可以设定最大的递归次数

WITH CTE_Customer(Id,NickName,VerifyStatus,Score)
AS
(
    --基本语句
    SELECT c.Id,c.Nickname,c.VerifyStatus,c.Score FROM Customer AS c WHERE c.AvatarId IS NULL
    UNION ALL
    --递归语句
    SELECT u.Id,u.Nickname,u.VerifyStatus,u.Score FROM Customer AS u
    INNER JOIN CTE_Customer cc
    ON u.Id = cc.Id WHERE u.Id  IN (3)
)

SELECT * FROM CTE_Customer
--限制递归次数
OPTION(MAXRECURSION 2)