在Snowflake SQL中实现Qlik Hierarchy函数，修正层级显示错误

问题核心 #

你的递归CTE出现Level2值混入Level1的问题，本质是初始锚点查询没有精准定位根节点，错误地将非顶层节点也纳入第一层，导致后续层级逻辑混乱。

正确实现方案 #

以下是修正后的递归CTE代码，可精准生成层级与完整路径：

1. 表结构与测试数据 #

假设你的层级表结构如下：

CREATE TABLE hierarchy_table (
    parent_node VARCHAR,
    child_node VARCHAR
);

测试数据：

INSERT INTO hierarchy_table VALUES
('ROOT', 'Level1_A'),
('Level1_A', 'Level2_A1'),
('Level1_A', 'Level2_A2'),
('ROOT', 'Level1_B'),
('Level1_B', 'Level2_B1');

2. 修正后的递归CTE代码 #

WITH RECURSIVE hierarchy_cte AS (
    -- 锚点查询：仅根节点作为Level 1，初始化路径
    SELECT
        parent_node,
        child_node,
        1 AS level,
        '/' || child_node AS path
    FROM hierarchy_table
    WHERE parent_node = 'ROOT' -- 根据实际场景调整根节点判断条件，比如parent_node IS NULL

    UNION ALL

    -- 递归查询：基于上一层子节点关联下一层，层级递增，路径追加当前节点
    SELECT
        ht.parent_node,
        ht.child_node,
        hc.level + 1 AS level,
        hc.path || '/' || ht.child_node AS path
    FROM hierarchy_table ht
    JOIN hierarchy_cte hc ON ht.parent_node = hc.child_node
)
SELECT * FROM hierarchy_cte
ORDER BY level, path;

3. 关键修正点 #

• 精准过滤根节点：锚点查询只保留最顶层节点作为Level 1，避免非根节点混入第一层。
• 严格递归关联：每次递归仅用上层的child_node匹配当前层的parent_node，保证层级递进逻辑正确。
• 连贯路径拼接：基于上层路径追加当前节点，生成完整的层级路径。

输出对比 #

错误输出（原代码） #

期望输出（修正后代码） #

场景适配 #

如果你的根节点判断条件是parent_node IS NULL，只需修改锚点查询的WHERE子句：

WHERE parent_node IS NULL

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