Snowflake中展开层级表所有实体连接至行的实现咨询

要获取所有直接和间接的实体连接关系，包括中间层级（比如1→3、2→4），你可以用以下两种方案，取决于你的数据库是否支持递归语法：

方案1：递归CTE（推荐，支持MySQL 8+、PostgreSQL、SQL Server等） #

递归CTE可以自动遍历所有层级，同时保留每一步的连接关系，不会只返回最终的最深层连接。

示例SQL #

WITH RECURSIVE entity_connections AS (
    -- 锚点：取出所有直接连接关系（深度1）
    SELECT 
        entity_id AS start_id,
        entity_type AS start_type,
        resource_id AS end_id,
        resource_type AS end_type,
        1 AS depth
    FROM your_table
    UNION ALL
    -- 递归：逐层向下连接，生成所有间接关系
    SELECT 
        ec.start_id,
        ec.start_type,
        t.resource_id AS end_id,
        t.resource_type AS end_type,
        ec.depth + 1 AS depth
    FROM entity_connections ec
    JOIN your_table t ON ec.end_id = t.entity_id
    WHERE ec.depth < 5 -- 限制最大深度为5，避免无限递归
)
-- 输出所有直接+间接连接，去重后排序
SELECT DISTINCT start_id, start_type, end_id, end_type
FROM entity_connections
ORDER BY start_id, depth;

效果说明 #

• 锚点部分先获取1→2、2→3、3→4这些直接关系
• 递归部分会自动生成：
  • 从1→2连接2→3得到1→3（深度2）
  • 从1→3连接3→4得到1→4（深度3）
  • 从2→3连接3→4得到2→4（深度2）
• 最终结果包含所有层级的连接关系，不会遗漏中间项

方案2：多自连接+UNION ALL（兼容老版本数据库，如MySQL 5.x） #

如果你的数据库不支持递归CTE，可以通过多次自连接，把每一层的间接关系单独查询，再用UNION ALL合并所有结果。

示例SQL #

-- 直接连接（深度1）
SELECT entity_id AS start_id, entity_type AS start_type, resource_id AS end_id, resource_type AS end_type
FROM your_table
UNION ALL
-- 深度2的间接连接（如1→3、2→4）
SELECT t1.entity_id, t1.entity_type, t2.resource_id, t2.resource_type
FROM your_table t1
JOIN your_table t2 ON t1.resource_id = t2.entity_id
UNION ALL
-- 深度3的间接连接（如1→4）
SELECT t1.entity_id, t1.entity_type, t3.resource_id, t3.resource_type
FROM your_table t1
JOIN your_table t2 ON t1.resource_id = t2.entity_id
JOIN your_table t3 ON t2.resource_id = t3.entity_id
UNION ALL
-- 深度4的间接连接（按需添加）
SELECT t1.entity_id, t1.entity_type, t4.resource_id, t4.resource_type
FROM your_table t1
JOIN your_table t2 ON t1.resource_id = t2.entity_id
JOIN your_table t3 ON t2.resource_id = t3.entity_id
JOIN your_table t4 ON t3.resource_id = t4.entity_id
UNION ALL
-- 深度5的间接连接（按需添加）
SELECT t1.entity_id, t1.entity_type, t5.resource_id, t5.resource_type
FROM your_table t1
JOIN your_table t2 ON t1.resource_id = t2.entity_id
JOIN your_table t3 ON t2.resource_id = t3.entity_id
JOIN your_table t4 ON t3.resource_id = t4.entity_id
JOIN your_table t5 ON t4.resource_id = t5.entity_id
-- 排序结果
ORDER BY start_id, end_id;

效果说明 #

每一段查询对应一个深度的连接：

• 第一段是直接连接
• 第二段是跨1层的间接连接
• 第三段是跨2层的间接连接，以此类推
• 通过UNION ALL把所有层级的结果合并，就能得到完整的连接关系集合

注意事项 #

• 替换SQL中的your_table为你的实际表名
• 如果数据存在循环引用（如A→B→A），递归CTE需要添加额外条件（比如AND ec.start_id != t.resource_id）避免无限递归
• 若存在重复的连接路径，可在最终查询中添加DISTINCT去重

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