SQL中能否重复引用同一个递归查询？如何优化重复CTE写法？

你当前重复定义了两次完全相同的递归CTE，确实可以只生成一次结果就复用，以下是两种适配不同场景的实现方案：

方案1：单WITH块复用（适用于PostgreSQL、SQL Server等支持同WITH下多DML操作的数据库） #

仅定义一次递归CTE即可同时供两次删除操作调用，逻辑和原有写法完全一致：

WITH RECURSIVE all_folders (id) AS (
    SELECT f.id
    FROM folder f
    WHERE f.id = 33
    UNION ALL
    SELECT f.id
    FROM folder f
    -- 原别名of是SQL保留关键字，这里替换为af避免语法错误
    JOIN all_folders af ON af.id = f.parent_folder_id
),
delete_samples AS (
    DELETE FROM sample
    WHERE parent_folder_id IN (SELECT id FROM all_folders)
    RETURNING 1
)
DELETE FROM folder
WHERE id IN (SELECT id FROM all_folders);

方案2：临时表方案（全数据库兼容） #

兼容MySQL、Oracle等所有支持递归CTE的数据库，临时表仅当前会话生效，不会残留数据：

-- 生成一次性临时表存储所有要删除的文件夹ID
CREATE TEMP TABLE temp_deleted_folders AS
WITH RECURSIVE all_folders (id) AS (
    SELECT f.id
    FROM folder f
    WHERE f.id = 33
    UNION ALL
    SELECT f.id
    FROM folder f
    JOIN all_folders af ON af.id = f.parent_folder_id
)
SELECT id FROM all_folders;

-- 先删除关联的sample数据
DELETE FROM sample WHERE parent_folder_id IN (SELECT id FROM temp_deleted_folders);
-- 再删除文件夹本身
DELETE FROM folder WHERE id IN (SELECT id FROM temp_deleted_folders);

-- 可选：用完主动清理临时表
DROP TABLE temp_deleted_folders;

现有写法的不足 #

• 重复执行两次递归查询，当文件夹层级深、数据量大时，查询开销直接翻倍
• 两次递归查询的执行间隙如果有其他写入操作修改了folder的层级结构，会导致两次查询返回的ID集合不一致，出现sample数据漏删、文件夹错删的问题
• 原代码中使用of作为递归CTE的别名，of是SQL保留关键字，在多数数据库中会直接触发语法错误

可优化空间 #

• 优先使用上述两种复用递归结果的方案，避免重复计算
• 给folder.parent_folder_id、sample.parent_folder_id字段添加普通索引，递归查询的关联效率和DELETE的匹配效率会有数量级的提升
• 如果单次要删除的文件夹数量超过万级，建议给DELETE语句加分批限制，比如每次删除1000条循环执行，避免长时间锁表影响正常业务
• 务必保持先删sample关联数据、再删folder数据的顺序，避免外键约束报错或者产生无效的脏数据

内容的提问来源于stack exchange，提问作者Tobiq