IT评测·应用市场-qidao123.com

标题: 通用表表达式 [打印本页]

作者: 道家人 时间: 2023-3-9 01:14
标题: 通用表表达式
通用表表达式

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

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

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

复制代码

-- 也可以定义多个
with t(n) as(
select 1
),
t2(m) as(
select 1
)
select * from t cross join t2;

复制代码

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

with t(n) as(
select 1
),
t2(m) as(
select n+1 from t
)
select * from t cross join t2;

复制代码

with子句

--查询每个部门的平均薪资
select d.department_name ,ds.avg_sal
from departments d
join (select department_id,avg(salary) avg_sal from employees group by department_id) ds
on d.department_id =ds.department_id
with department_avg(department_id,avg_sal) as (
select department_id,avg(salary) avg_sal from employees group by department_id
)
select d.department_name ,department_avg.avg_sal
from departments d
join department_avg
on d.department_id =department_avg.department_id;

复制代码

递归

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

WITH RECURSIVE cte_name AS(
cte_query_initial -- 初始化部分
UNION [ALL]
cte_query_iterative -- 递归部分
) 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

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)