第三章 通用表表达式
三、通用表表达式
3.1、非递归CTE
3.1.1、CTE的使用
-- 不适用CTE
select * from (select 1) as dt;
-- 使用CTE
with dt as (select 1)
select * from dt;
-- 多个通用表变量的使用
with cte1(id) as (select 1),
cte2 as (select id+1 from cte1)
select * from cte1 join cte2;
3.2、递归 CTE
如下图所示,使用递归的CTE之后,结果如右侧所示;
3.2.1、递归 CTE使用举例(查询一个员工和经理的路径)
下图为雇员表的结构和数据;
使用递归CTE查询数据
该功能主要方便了我们对数据在有上下级的情况下的遍历,类似找路径
3.3、递归限制
3.3.1、cte_max_recursion_depth
with recursive cte(n) as
( select 1
union all
select n+1 from cte
)
select * from cte;
如下图所示,在循环了1000次之后,出现警告,并且提示我们可以通过修改"cte_max_recursion_depth"这个值来扩大循环的次数。
-- 展示参数cte_max_recursion_depth
show variables like 'cte_max%';
从查询结果中,我们可以看到默认的循环次数为1000;
-- 在当前会话设置我们的循环次数
set session cte_max_recursion_depth=10;
从下图可见,此时的循环上限变为了10;
3.3.2、max_execution_time
-- 查看执行时间
show variables like 'max_execution%';
-- 设置当前会话的循环执行时间为1秒
set session max_execution_time=1000;
从下图中可见,当我们的执行时间先到的时候,sql会先被执行时间的约束限制;