Sql Server 公用表达式 CTE

对于select查询语句来说，通常情况下，为了使T-SQL代码更加简洁和可续,在一个查询中引入另外的结果集都是通过视图而不是子查询来进行分解的，但是，视图是作为系统对象存在数据库中，那对于结果集仅仅需要在存储过程或是用户自定义函数中使用一次的时候，使用视图就显得有些奢侈了。
公用表表达式其实提供的功能和视图差不多，但是它不像视图一样把SQL语句保存在我们的数据库里面。虽然CTE不是必需的，但是它可以为提高SQL的可读性。

微软官方给的使用CET的优势：

• 编写一个递归查询（类似树查询）
• 使用要使用一个类似视图的功能，但是又不想把这个查询sql语句的定义保存在数据库
• 要引用一个返回数据sql语句多次，只需要定义一次。

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

公用表达式的定义非常简单，只包含三部分：
公用表表达式的名字(在WITH之后）
所涉及的列名(可选)
一个SELECT语句(紧跟AS之后)

WITH expression_name [(column_name [,...n] )] AS
(
     cte_query_definition
)

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

非递归公用表表达式CTE

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

--一个简单的例子：
WITH cte_member AS
(
  SELECT * FROM [member]
)
SELECT * FROM cte_member

--公用表表达式的好处之一是可以在接下来一条语句中多次引用:
WITH cte_member AS
(
  SELECT * FROM [member]
)
SELECT * FROM cte_member a INNER JOIN cte_member b ON a.id = b.pid

--由于 CTE 只能在接下来一条语句中使用,如果使用多次会提示'cte_member'名称无效
WITH cte_member AS
(
  SELECT * FROM [member]
)
SELECT * FROM cte_member
SELECT * FROM cte_member --不能多次使用

--因此，当需要接下来的一条语句中引用多个 **CTE** 时，可以定义多个，中间用逗号分隔。
WITH cte_member1 AS
(
  SELECT * FROM [member]
),
cte_member2 AS
(
  SELECT * FROM [member]
)
SELECT * FROM cte_member1
UNION
SELECT * FROM cte_member2

递归公用表表达式(CTE)

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

• 基本语句
• 递归语句

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

比如典型的树形结构，pid作为上级id，有上下级关系的用户表。

-- 根据某用户id获取所有上级
WITH cte_member AS
(
  SELECT id,pid,name FROM [member] WHERE [id]=3
  UNION ALL
  SELECT a.id,a.pid,a.name FROM [member] a INNER JOIN cte_member b ON a.id=b.pid
)
SELECT * FROM cte_member;

-- 根据某用户id获取所有下级
WITH cte_member AS
(
  SELECT id,pid,depth,name FROM [member] WHERE [id]=3 --使用id,结果集中包含自身,使用pid不包含自身
  UNION ALL
  SELECT a.id,a.pid,a.depth,a.name FROM [member] a INNER JOIN cte_member b ON a.pid=b.id
)
SELECT * FROM cte_member;

使用id筛选

使用pid筛选