Snowflake中COMPANIES表7级层级视图构建求助

以下是满足需求的CompanyHierarchyView视图创建语句，用递归CTE实现根节点到最多7级的层级展示：

CREATE OR REPLACE VIEW CompanyHierarchyView AS
WITH RECURSIVE CompanyHierarchy AS (
    -- 初始层：取根节点的直接子节点（层级2）
    SELECT
        PARENTCOMPANY AS LVL1_COMPANY,
        PARENTID AS LVL1_ID,
        CHILDCOMPANY AS LVL2_COMPANY,
        CHILDID AS LVL2_ID,
        NULL AS LVL3_COMPANY,
        NULL AS LVL3_ID,
        NULL AS LVL4_COMPANY,
        NULL AS LVL4_ID,
        NULL AS LVL5_COMPANY,
        NULL AS LVL5_ID,
        NULL AS LVL6_COMPANY,
        NULL AS LVL6_ID,
        NULL AS LVL7_COMPANY,
        NULL AS LVL7_ID,
        2 AS HIERARCHY_LEVEL
    FROM COMPANIES
    WHERE PARENTID IS NULL -- 根节点判定：若实际数据中根节点是PARENTID无对应CHILDID，可替换为PARENTID NOT IN (SELECT CHILDID FROM COMPANIES)
    
    UNION ALL
    
    -- 递归层：逐层关联子公司，直到第7级
    SELECT
        ch.LVL1_COMPANY,
        ch.LVL1_ID,
        ch.LVL2_COMPANY,
        ch.LVL2_ID,
        CASE WHEN ch.HIERARCHY_LEVEL = 2 THEN c.CHILDCOMPANY ELSE ch.LVL3_COMPANY END AS LVL3_COMPANY,
        CASE WHEN ch.HIERARCHY_LEVEL = 2 THEN c.CHILDID ELSE ch.LVL3_ID END AS LVL3_ID,
        CASE WHEN ch.HIERARCHY_LEVEL = 3 THEN c.CHILDCOMPANY ELSE ch.LVL4_COMPANY END AS LVL4_COMPANY,
        CASE WHEN ch.HIERARCHY_LEVEL = 3 THEN c.CHILDID ELSE ch.LVL4_ID END AS LVL4_ID,
        CASE WHEN ch.HIERARCHY_LEVEL = 4 THEN c.CHILDCOMPANY ELSE ch.LVL5_COMPANY END AS LVL5_COMPANY,
        CASE WHEN ch.HIERARCHY_LEVEL = 4 THEN c.CHILDID ELSE ch.LVL5_ID END AS LVL5_ID,
        CASE WHEN ch.HIERARCHY_LEVEL = 5 THEN c.CHILDCOMPANY ELSE ch.LVL6_COMPANY END AS LVL6_COMPANY,
        CASE WHEN ch.HIERARCHY_LEVEL = 5 THEN c.CHILDID ELSE ch.LVL6_ID END AS LVL6_ID,
        CASE WHEN ch.HIERARCHY_LEVEL = 6 THEN c.CHILDCOMPANY ELSE ch.LVL7_COMPANY END AS LVL7_COMPANY,
        CASE WHEN ch.HIERARCHY_LEVEL = 6 THEN c.CHILDID ELSE ch.LVL7_ID END AS LVL7_ID,
        ch.HIERARCHY_LEVEL + 1 AS HIERARCHY_LEVEL
    FROM CompanyHierarchy ch
    JOIN COMPANIES c ON ch.CHILDID = c.PARENTID
    WHERE ch.HIERARCHY_LEVEL < 7 -- 限制最大层级为7
)
-- 合并根节点本身（无子女的情况）和所有层级数据
SELECT * FROM CompanyHierarchy
UNION ALL
SELECT
    PARENTCOMPANY AS LVL1_COMPANY,
    PARENTID AS LVL1_ID,
    NULL AS LVL2_COMPANY,
    NULL AS LVL2_ID,
    NULL AS LVL3_COMPANY,
    NULL AS LVL3_ID,
    NULL AS LVL4_COMPANY,
    NULL AS LVL4_ID,
    NULL AS LVL5_COMPANY,
    NULL AS LVL5_ID,
    NULL AS LVL6_COMPANY,
    NULL AS LVL6_ID,
    NULL AS LVL7_COMPANY,
    NULL AS LVL7_ID,
    1 AS HIERARCHY_LEVEL
FROM COMPANIES
WHERE PARENTID IS NULL
AND NOT EXISTS (SELECT 1 FROM COMPANIES c WHERE c.PARENTID = COMPANIES.PARENTID);

核心修正说明： #

• 根节点全覆盖：单独补充无子女的根节点，避免这类节点被遗漏。
• 递归逻辑严谨：通过HIERARCHY_LEVEL字段严格控制递归深度，确保不超过7级；用CASE语句精准填充对应层级的公司信息，避免字段错位。
• 关联条件准确：递归时用当前层级的子ID关联下一层的父ID，保证层级关系链条正确。

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