SQL Server条件递归查询：实现层级Setting值继承求助

我有以下两张表：

表1 #

表2 #

业务逻辑 #

若在表2中未找到对应节点的Setting值，则沿表1定义的层级向上查找，直至找到有效值。

示例场景 #

• 查询C2的Setting值，直接返回1（C2在表2中的直接值）
• 查询C1的Setting值，返回5（C1和其父节点P1在表2中均无值，最终找到祖父节点GP1的值）
• 查询P2的Setting值，返回5（其父节点GP1在表2中有值）

我尝试实现递归查询但未成功，目前写出的代码如下：

with settings as
(
    select N.Id,N.ParentId, N.ShortName,DTC.Setting 
    from Table1 as N 
    left join Table2 as DTC on DTC.Id=N.id
)
, RecursiveTable AS (
    SELECT no.Id,ShortName,ParentId,Setting 
    FROM settings as no
    UNION ALL
    SELECT n.Id,n.ShortName, n.ParentId,n.Setting 
    FROM settings n

    INNER JOIN RecursiveTable rn ON n.id = rn.ParentId
)
select * from RecursiveTable

你的递归查询方向错误，且未处理“找到有效值即停止”的逻辑，也未保留原始节点的关联关系。以下是修正后的代码：

WITH RecursiveSettings AS (
    -- 锚点成员：获取每个节点自身的Setting，同时保留原始节点ID
    SELECT 
        t1.Id AS OriginalId,
        t1.Id,
        t1.ParentId,
        t2.Setting
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.Id = t2.Id

    UNION ALL

    -- 递归成员：仅当当前未找到有效值时，向上遍历父节点
    SELECT 
        rs.OriginalId,
        t1.Id,
        t1.ParentId,
        t2.Setting
    FROM RecursiveSettings rs
    JOIN Table1 t1 ON rs.ParentId = t1.Id
    LEFT JOIN Table2 t2 ON t1.Id = t2.Id
    WHERE rs.Setting IS NULL
)
-- 筛选每个原始节点的首个有效值
SELECT 
    OriginalId,
    t1.ShortName,
    MAX(Setting) AS InheritedSetting
FROM RecursiveSettings rs
JOIN Table1 t1 ON rs.OriginalId = t1.Id
WHERE Setting IS NOT NULL
GROUP BY OriginalId, t1.ShortName
ORDER BY OriginalId;

代码说明 #

1. 锚点成员：初始化每个节点的查询，保留OriginalId用于最终关联原始节点，避免递归过程中丢失初始查询对象。
2. 递归成员：仅在当前节点无有效值时，继续向上查找父节点，减少不必要的递归层级。
3. 最终筛选：通过GROUP BY和MAX（每个原始节点仅会出现一个非空有效值）提取最终继承的Setting值，关联表1补充节点名称。

执行后将得到符合预期的结果：

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