SQL中WITH RECURSIVE能否作为子查询？递归获取住宿对应楼宇

当然可以把WITH RECURSIVE作为子查询使用，而且结合你的层级表结构，我们可以直接用递归CTE关联stay表，一次性拿到所有住宿记录对应的楼宇信息，下面是具体的实现思路和示例：

先明确表结构逻辑 #

• stay表的每条记录通过building_unit_uid关联到房间级的building_unit
• building_unit是层级结构：房间→楼层→楼宇，其中楼宇的building_unit_uid通常为NULL（或通过type字段标记为楼宇类型）

完整实现SQL（直接用CTE关联） #

这是最清晰的写法，递归CTE先遍历所有单元的层级关系，找到每个单元对应的顶层楼宇，再和stay表关联：

WITH RECURSIVE unit_hierarchy AS (
    -- 锚点：初始化所有单元的基础信息，默认自己为根节点
    SELECT 
        uid AS unit_uid,
        building_unit_uid AS parent_uid,
        type,
        uid AS top_building_uid
    FROM building_unit
    
    UNION ALL
    
    -- 递归成员：向上遍历父节点，直到找到楼宇类型（或无父节点）
    SELECT 
        uh.unit_uid,
        bu.building_unit_uid AS parent_uid,
        bu.type,
        bu.uid AS top_building_uid  -- 替换为父节点ID，逐步向上找楼宇
    FROM unit_hierarchy uh
    JOIN building_unit bu ON uh.parent_uid = bu.uid
    -- 终止条件：如果父节点已经是楼宇，就停止递归
    WHERE bu.type != 'building'
)
-- 关联stay表，提取每条住宿记录对应的楼宇
SELECT 
    s.uid AS stay_id,
    uh.top_building_uid AS building_id,
    bu.type AS building_type,
    bu.uid AS building_unit_id
FROM stay s
-- 关联到递归后的层级表，找到住宿记录对应的单元的顶层楼宇
JOIN unit_hierarchy uh ON s.building_unit_uid = uh.unit_uid
-- 关联回building_unit表，确认最终是楼宇节点
JOIN building_unit bu ON uh.top_building_uid = bu.uid
WHERE bu.type = 'building'
-- 去重：避免递归过程中产生的重复路径
GROUP BY s.uid, uh.top_building_uid, bu.type, bu.uid;

将WITH RECURSIVE作为子查询的写法 #

如果你确实需要把递归逻辑放在子查询里，也是完全可行的，比如：

SELECT 
    s.uid AS stay_id,
    sub.top_building_uid AS building_id
FROM stay s
JOIN (
    WITH RECURSIVE unit_hierarchy AS (
        SELECT 
            uid AS unit_uid,
            building_unit_uid AS parent_uid,
            uid AS top_building_uid,
            type
        FROM building_unit
        
        UNION ALL
        
        SELECT 
            uh.unit_uid,
            bu.building_unit_uid,
            bu.uid,
            bu.type
        FROM unit_hierarchy uh
        JOIN building_unit bu ON uh.parent_uid = bu.uid
        WHERE bu.type != 'building'
    )
    -- 子查询里直接过滤出每个单元对应的楼宇节点
    SELECT unit_uid, top_building_uid
    FROM unit_hierarchy
    WHERE type = 'building'
) sub ON s.building_unit_uid = sub.unit_uid;

注意事项 #

• 如果你的building_unit表没有type字段区分层级类型，可以把终止条件改成bu.building_unit_uid IS NOT NULL，最后过滤parent_uid IS NULL的节点（即顶层楼宇）
• 递归时要确保有明确的终止条件，避免无限递归
• 如果层级结构可能有重复路径，记得用GROUP BY或者DISTINCT去重

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