SQLite闭包表：如何优化拆分树结构的SQL避免重复子查询

问题场景 #

现有基于闭包表模型设计的树路径存储表，结构定义如下：

CREATE TABLE treepaths(
    tpa_ance INTEGER NOT NULL, -- 祖先节点ID
    tpa_desc INTEGER NOT NULL, -- 后代节点ID
    tpa_leng INTEGER NOT NULL, -- 路径长度
    UNIQUE(tpa_ance, tpa_desc)
);

表初始存储了一条1→2→3→4→5→6的链式树数据，初始路径记录如下：

1|1|0
1|2|1
1|3|2
1|4|3
1|5|4
1|6|5
2|2|0
2|3|1
2|4|2
2|5|3
2|6|4
3|3|0
3|4|1
3|5|2
3|6|3
4|4|0
4|5|1
4|6|2
5|5|0
5|6|1
6|6|0

原有拆分逻辑为将节点4的子树从原树分离，使用的SQL如下：

DELETE FROM treepaths WHERE tpa_desc IN 
(SELECT tpa_desc FROM treepaths WHERE tpa_ance = 4 and tpa_leng <> 0)
AND tpa_ance NOT IN
(SELECT tpa_desc FROM treepaths WHERE tpa_ance = 4 and tpa_leng <> 0);

执行后结果符合预期，成功切断原树到4子树（节点5、6）的路径，保留原树到节点4的路径、节点4自连接、5-6子树内部路径：

1|1|0
1|2|1
1|3|2
1|4|3
2|2|0
2|3|1
2|4|2
3|3|0
3|4|1
4|4|0
5|5|0
5|6|1
6|6|0

但上述SQL重复书写了两次完全相同的子查询，维护性差，需要在SQLite环境下优化写法。

优化方案 #

SQLite 3.8.3及以上版本（当前主流发行版均已满足）支持WITH公共表表达式（CTE），可以将重复使用的子查询提前定义一次后复用，完全兼容原有逻辑，优化后SQL如下：

WITH subtree_nodes AS (
    -- 一次查询取出待拆分子树的所有内部节点（排除拆分节点4自身）
    SELECT tpa_desc AS node_id
    FROM treepaths
    WHERE tpa_ance = 4 AND tpa_leng <> 0
)
DELETE FROM treepaths
WHERE 
    tpa_desc IN (SELECT node_id FROM subtree_nodes)
    AND tpa_ance NOT IN (SELECT node_id FROM subtree_nodes);

方案说明 #

• 逻辑和原SQL100%等价，执行结果完全一致
• 子查询仅需编写一次，后续如果需要调整拆分的根节点，仅需修改WITH子句内的tpa_ance = 4条件即可，无需改动DELETE主体逻辑，维护成本更低
• SQLite优化器会将CTE查询结果临时物化，不会重复执行两次子查询扫描，性能相比原写法有小幅提升

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