SQL | CTE公用表表达式

一个临时的结果集。定义在内存中，不产生I/O，可以自己引用，也可以在查询中被多次引用。

用法与with as 的含义

with as 做子查询的部分
其用于定义一个sql片段，被整个sql语句用到。若 with as 所定义的表名被调用两次以上，则优化器会自动将 with as 所获取的数据放入临时表里，仅一次则不会。可通过 materialize 将 with as 短语中的数据强制放入全局临时表里。
其可以被紧跟着的一条sql语句使用多次，但在此之后不能再使用。

with CTE_test [(colname[, ...n])] -- CTE_test是表达式名称，后面可接列名，亦可空
as(
  select * from table             -- 定义其结果集的查询语句 
);
select * from CTE_test as a  -- 第一次引用
inner join CTE_test as b -- 第二次引用
on a.id=b.id 
order by a.id desc

CTE优点：

1. 可定义递归公用表的表达式
2. 当不需要将结果集作为视图被多方引用时，CTE可以使其更简洁
3. group by 可直接作用与子查询所得到的标量列
4. 可在一个语句中多次引用公式表表达式（CTE）

   递归CTE和非递归CTE

   前述的仅可就近使用一次的为非递归CTE
   递归CTE即是在 as 后面的select语句中调用其本身，常见于带有层级关系的自连接查询 ```sql — 查询名称层级 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 — 当执行所需超过最大递归数时，会提示语句被终止 ```