层级数据过滤方案咨询及表架构合理性确认

1. Category + Mapping表架构的合理性与效率分析 #

这个架构是合理且适配多父节点层级数据维护需求的，核心优势如下：

• 职责分离清晰：Category表专注存储类别核心属性（ID、Label），Mapping表单独维护多父节点的关联关系，既避免了Category表因多继承产生的数据冗余，也便于独立调整层级关联规则。
• 扩展性强：Mapping表的QualifierID字段可用于区分不同业务场景下的关联逻辑（比如同一类别在不同规则下的不同父节点），后续扩展空间充足。
• 维护成本低：新增或修改层级关联时，仅需操作Mapping表，不会影响Category表的核心数据。

关于效率优化，需注意以下几点：

• 给Mapping表建立组合索引：比如(ItemID, ParentID)、(ParentID, ItemID)，能大幅提升递归CTE或层级查询的执行速度。
• 增加数据约束：给Mapping表的ItemID和ParentID添加外键关联Category表的ID，避免无效的层级关联数据。
• 大数据量场景下：可预计算层级路径（比如用/1/2/3/格式存储完整路径），或用物化视图缓存常用的层级查询结果，减少递归计算的开销。

2. 数据库层面过滤含Level=3子节点的完整层级实现方法 #

核心思路是：先定位所有Level=3的节点，再反向递归获取它们的所有祖先节点，最终得到包含这些节点的完整层级路径。以下是两种常用实现方式：

方式一：基于已有递归CTE反向追溯 #

假设你已生成的完整层级CTE名为cte_full_hierarchy（包含ParentID、ID、Label、CategoryLevel字段），可通过以下SQL实现：

WITH cte_level3_ancestors AS (
    -- 第一步：取出所有Level=3的节点
    SELECT ID, Label, ParentID, CategoryLevel
    FROM cte_full_hierarchy
    WHERE CategoryLevel = 3
    -- 第二步：递归向上查找所有父节点
    UNION ALL
    SELECT fh.ID, fh.Label, fh.ParentID, fh.CategoryLevel
    FROM cte_full_hierarchy fh
    INNER JOIN cte_level3_ancestors l3a ON fh.ID = l3a.ParentID
)
-- 按层级排序输出完整结构
SELECT * 
FROM cte_level3_ancestors
ORDER BY CategoryLevel, ParentID, ID;

方式二：在递归CTE中直接标记并过滤 #

若不想依赖预先生成的完整层级CTE，可直接在递归过程中标记目标路径节点：

WITH cte_filtered_hierarchy AS (
    -- 锚点成员：筛选根节点（假设根节点ParentID为NULL）
    SELECT 
        c.ID,
        m.ParentID,
        c.Label,
        1 AS CategoryLevel,
        -- 标记当前节点是否存在Level=3的后代
        CASE WHEN EXISTS (
            SELECT 1 
            FROM Mapping m1
            JOIN Category c1 ON m1.ItemID = c1.ID
            WHERE m1.ParentID = c.ID
            AND EXISTS (
                SELECT 1 
                FROM Mapping m2
                JOIN Category c2 ON m2.ItemID = c2.ID
                WHERE m2.ParentID = c1.ID
                AND EXISTS (
                    SELECT 1 
                    FROM Mapping m3
                    JOIN Category c3 ON m3.ItemID = c3.ID
                    WHERE m3.ParentID = c2.ID
                )
            )
        ) THEN 1 ELSE 0 END AS HasLevel3Descendant
    FROM Category c
    LEFT JOIN Mapping m ON c.ID = m.ItemID
    WHERE m.ParentID IS NULL
    UNION ALL
    -- 递归成员：遍历子节点并继承标记
    SELECT 
        c.ID,
        m.ParentID,
        c.Label,
        fh.CategoryLevel + 1,
        -- 当前节点是Level=3，或父节点已有Level=3后代则标记为1
        CASE WHEN fh.CategoryLevel + 1 = 3 OR fh.HasLevel3Descendant = 1 THEN 1 ELSE 0 END
    FROM Mapping m
    JOIN Category c ON m.ItemID = c.ID
    JOIN cte_filtered_hierarchy fh ON m.ParentID = fh.ID
)
-- 过滤出目标路径的所有节点
SELECT ID, ParentID, Label, CategoryLevel
FROM cte_filtered_hierarchy
WHERE HasLevel3Descendant = 1
ORDER BY CategoryLevel, ParentID, ID;

两种方式的区别：方式一依赖已有完整层级CTE，代码更简洁；方式二直接从原始表递归，适合无需预先生成全量层级的场景。

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