如何对path列存储的层级树用SQL筛选指定继承标记子树并统计数量

• 先约定表名：节点路径表为node_paths（字段id、path），节点开关状态表为node_switch_status（字段id、is_turned_on）
• 对每个节点，匹配所有路径为当前节点路径前缀的节点（包含自身和所有祖先节点），筛选出其中有显式开关记录的条目
• 取路径长度最长的匹配条目（对应最近的祖先/节点自身）的开关值，无匹配则默认值为true
• 最后按开关值分组统计总数即可

WITH node_all_matches AS (
    -- 关联每个节点的所有符合前缀匹配的开关记录
    SELECT
        p.id AS node_id,
        s.is_turned_on,
        -- 按匹配路径长度倒序，最长的就是最近的父节点/自身
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY LENGTH(p2.path) DESC) AS match_rank
    FROM node_paths p
    -- 关联所有是当前节点前缀的节点（祖先+自身）
    LEFT JOIN node_paths p2 ON p.path LIKE CONCAT(p2.path, '%')
    -- 关联这些节点的开关记录
    LEFT JOIN node_switch_status s ON p2.id = s.id
),
node_final_status AS (
    -- 取每个节点的最终开关状态
    SELECT
        node_id,
        COALESCE(MAX(CASE WHEN match_rank = 1 THEN is_turned_on END), TRUE) AS final_is_turned_on
    FROM node_all_matches
    GROUP BY node_id
)
-- 统计结果
SELECT
    final_is_turned_on,
    COUNT(*) AS node_count
FROM node_final_status
GROUP BY final_is_turned_on;

说明：如果使用的数据库不支持CTE语法，可将CTE逻辑改为子查询嵌套实现，核心匹配逻辑不变。

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