通用表表达式

通用表表达式

通用表表达式（Common Table Expression、CTE）是一个临时的查询结果或者临时表，可以 在其他 SELECT、INSERT、UPDATE 以及 DELETE 语句中使用。通用表表达式只在当前语句中 有效，类似于子查询。
使用 CTE 的主要好处包括：

• 提高复杂查询的可读性。CTE 可以将复杂查询模块化，组织成容易理解的结构。
  
• 支持递归查询。CTE 通过引用自身实现递归，可以方便地处理层次结构数据和图数据。
  

1. --临时表t 一个字段为n 括号里是表的内容
2. with t(n) as(
3.         select 1
4. )
5. select * from  t;

复制代码

1. -- 也可以定义多个
2. with t(n) as(
3.         select 1
4. ),
5. t2(m) as(
6.         select 1
7. )
8. select * from  t cross join t2;

复制代码

相当于一个变量，可以重复使用, 后面的临时表可以应用前面临时表的变量

1. with t(n) as(
2.         select 1
3. ),
4. t2(m) as(
5.         select n+1 from  t
6. )
7. select * from  t cross join t2;

复制代码

with子句

1. --查询每个部门的平均薪资
2. select d.department_name ,ds.avg_sal
3. from departments d
4. join (select department_id,avg(salary) avg_sal from employees  group by department_id) ds
5. on d.department_id =ds.department_id
9. with department_avg(department_id,avg_sal) as (
10.                 select department_id,avg(salary) avg_sal from employees  group by department_id
11. )
12. select d.department_name ,department_avg.avg_sal
13. from departments d
14. join department_avg
15. on d.department_id =department_avg.department_id;

复制代码

递归

递归 CTE 允许在它的定义中进行自引用，理论上来说可以实现任何复杂的计算功能，最常 用的场景就是遍历层次结构的数据和图结构数据。

1. WITH RECURSIVE cte_name AS(
2. cte_query_initial -- 初始化部分
3. UNION [ALL]
4. cte_query_iterative -- 递归部分
5. ) SELECT * FROM cte_name;

复制代码

• RECURSIVE 表示递归；
  
• cte_query_initial 是初始化查询，用于创建初始结果集；
  
• cte_query_iterative 是递归部分，可以引用 cte_name；
  
• 如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归并返回结果。
  

一个经典的递归 CTE 案例就是生成数字序列：
[code]with recursive t(n) as (        select 1   -- 初始化        union all             select n+1 from t  where n