SQL经典案例之数据库的CTE递归循环使用

SQL经典案例之数据库的CTE递归循环使用

• CTE（Common Table Expression）
• CTE的基本语法
• 生成数字序列
• 递归的终止

CTE（Common Table Expression）

数据库的CTE（Common Table Expression，公用表表达式）是一种在SQL查询中用于定义临时结果集的工具「 临时表（或称子查询） 」

CTE的基本语法

WITH cte_name AS (
    SELECT ... FROM ... WHERE ...
)
-- 使用你定义的CTE
SELECT * FROM cte_name;
复制

案例理解：
子查询1获取数值 1 + 子查询2获取数值 2
改成t1表和t2表子查询
再改用CTE写法的t1和t2临时表，会更直观，方便理解

-- Oracle
select sum((select 1 as n1 from dual) + (select 2 as n2 from dual)) from dual;

select sum(t1.n1+t2.n2) from (select 1 as n1 from dual) t1,(select 2 as n2 from dual) t2;

with t1 as(
	select 1 as n1 from dual
),
t2 as(
	select 2 as n2 from dual
)select sum(t1.n1+t2.n2) from t1,t2;


-- PostgreSQL
select sum((select 1 as n1) + (select 2 as n2)) ;

select sum(t1.n1+t2.n2) from (select 1 as n1) t1,(select 2 as n2) t2;

with t1 as(
	select 1 as n1
),
t2 as(
	select 2 as n2
)select sum(t1.n1+t2.n2) from t1,t2;

复制

关键字 RECURSIVE 表示递归形式的CTE（即递归CTE）

• Oracle 和 SQL Serer不支持关键字 RECURSIVE，直接使用WITH定义递归形式的CTE，且必须使用 UNION ALL 运算符
• SQLite可以省略关键字 RECURSIVE

WITH [RECURSIVE] cte_name AS (
    -- 初始查询（锚点成员/顶层根节点）
    SELECT ... FROM ... WHERE ...
    UNION [ALL]
    -- 递归查询（递归成员/递归遍历子层）
    SELECT ... FROM cte_name WHERE ...;
)
-- 使用你定义的CTE
SELECT * FROM cte_name;
复制

生成数字序列

-- MySQL、PostgreSQL、SQLite
WITH RECURSIVE t_serial(n) as(
    select 1
    union all
    select n+1 from t_serial where n<10
)
select n from t_serial;

-- Oracle
WITH t_serial(n) as(
    select 1 from dual
    union all
    select n+1 from t_serial where n<10
)
select n from t_serial;
复制

递归的终止

• 确保递归有明确的终止条件，以避免无限递归。
• 递归深度可能会受到数据库系统的限制，因此在实际应用中可能需要考虑性能问题和潜在的递归深度限制。
• 递归CTE在处理大型数据集时可能会非常耗时和占用大量内存，因此在使用时需要谨慎评估其对系统性能的影响。

生成数字序列的例子去掉where条件，让递归无终止条件，陷入死循环测试

-- MySQL、PostgreSQL、SQLite
WITH RECURSIVE t_serial(n) as(
    select 1
    union all
    select n+1 from t_serial
)
select n from t_serial;

-- Oracle
WITH t_serial(n) as(
    select 1 from dual
    union all
    select n+1 from t_serial
)
select n from t_serial;
复制

• MySQL默认递归1000次（由系统变量cte_max_recursion_depth控制）后终止并提示错误
• PostgreSQL和SQLite无限制，进入死循环
• Oracle能检查查询语句中的死循环问题并提示错误
• SQL Server默认递归100次（可在查询中使用MAXRECURSION选项进行设置）后终止并提示错误