SQLite自引用表递归计算节点及所有子节点score总和

实现方案 #

你可以通过以下两种方式实现需求：

方案一：利用现有full_slug字段（性能更优）

由于full_slug已经存储了完整路径，只要后代节点的full_slug是当前节点的路径或子路径，就属于当前节点的后代，直接关联统计即可：

SELECT
    e1.slug,
    SUM(e2.score) AS total_score
FROM ecosystems e1
INNER JOIN ecosystems e2
    ON e2.full_slug = e1.full_slug 
    OR e2.full_slug LIKE CONCAT(e1.full_slug, '/%')
GROUP BY e1.slug
ORDER BY e1.slug;

注：匹配条件增加/前缀校验，是为了避免出现slug为aaa和aaaa这类前缀相似的场景下误匹配，保证匹配的都是路径上的后代节点。

方案二：通用递归CTE实现（无full_slug也可使用）

如果没有冗余的full_slug字段，可以用递归CTE遍历每个节点的所有后代节点，再聚合计算：

WITH RECURSIVE eco_hierarchy AS (
    -- 锚点：将每个节点作为根节点，初始化自身得分
    SELECT 
        slug AS root_slug,
        slug,
        parent_slug,
        score
    FROM ecosystems
    UNION ALL
    -- 递归：逐层关联子节点，挂载到对应的根节点下
    SELECT 
        eh.root_slug,
        e.slug,
        e.parent_slug,
        e.score
    FROM eco_hierarchy eh
    INNER JOIN ecosystems e ON e.parent_slug = eh.slug
)
-- 按根节点分组求和，得到每个节点及其所有后代的总得分
SELECT 
    root_slug AS slug,
    SUM(score) AS total_score
FROM eco_hierarchy
GROUP BY root_slug
ORDER BY root_slug;

两种方案执行结果都和你给出的预期输出完全一致。

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