如何在SQL Server中查询所有门店对应的当前活跃父门店？

需求背景 #

现有store表包含以下字段：

• ID：门店ID
• ISACTIVE：是否活跃（1=活跃，0=已关闭）
• TRANSFERTONEXTSTOREID：门店转移目标ID（仅当ISACTIVE=0时有效）

需要获取所有门店对应的当前活跃父门店：

• 若门店本身活跃（ISACTIVE=1），父门店为自身；
• 若门店已关闭，需追溯其转移链直至找到活跃门店。

Oracle中已通过层级查询实现，SQL语句如下：

SELECT CONNECT_BY_ROOT ID AS store_id, id as current_id
FROM store
WHERE ISACTIVE = 1
CONNECT BY PRIOR TRANSFERTONEXTSTOREID = ID;

SQL Server解决方案 #

SQL Server没有Oracle的CONNECT BY层级语法，需使用**递归CTE（Common Table Expression）**实现相同逻辑，代码如下：

WITH RecursiveStores AS (
    -- 锚点成员：所有活跃门店，自身即为最终父门店
    SELECT 
        ID AS current_id,
        ID AS store_id
    FROM store
    WHERE ISACTIVE = 1

    UNION ALL

    -- 递归成员：追溯所有指向当前门店的已关闭门店，继承活跃父门店ID
    SELECT 
        rs.current_id,
        s.ID AS store_id
    FROM store s
    JOIN RecursiveStores rs 
        ON s.TRANSFERTONEXTSTOREID = rs.store_id
    WHERE s.ISACTIVE = 0
)
SELECT store_id, current_id
FROM RecursiveStores
ORDER BY store_id;

代码逻辑说明 #

1. 锚点成员：先筛选出所有活跃门店，此时每个活跃门店的store_id（原始门店ID）和current_id（最终活跃父门店ID）均为自身，符合“活跃门店父门店是自己”的规则。
2. 递归成员：关联store表中转移目标为递归CTE内门店的已关闭门店，将这些门店的ID作为新的store_id，同时继承对应的current_id（即最终的活跃父门店），以此逐层追溯整个转移链。
3. 最终查询：从递归CTE中取出所有门店的store_id和对应的current_id，得到需求结果。

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