SQL Server中Key值按父子层级结构向下传播的问题求助

你需要将SQL Server表中非NULL的Key值向下传播，核心规则是：

• 低层级为父，高层级为子；
• 非NULL Key的行作为起始点，其Key传播给所有后续Level大于它的行，直到遇到第一个Level≤它的行，此时传播终止；
• 若后续行本身有Key，会被起始行的Key覆盖；
• 同级或更低层级的行不会继承之前锚点行的Key，需重新寻找自己的锚点。

原代码的问题 #

你的现有查询逻辑仅根据「行号更小、层级更低、Key非空」来查找继承源，完全忽略了传播终止条件：如果当前行之前存在Level≤某个锚点行Level的行，该锚点行的传播应该在那一行就停止，不能再覆盖后续行。比如第9行是Level2（和第8行同级），第8行的Key不应该传播到第9行及之后的行，但原代码会忽略这个边界，导致第10行错误继承第8行的Key。

解决方案 #

方案一：带存在性检查的相关子查询 #

通过NOT EXISTS确保锚点行的传播范围未被终止，直接定位当前行对应的有效锚点：

SELECT
    C1.RowNumber,
    C1.Level,
    C1.PartNumber,
    C1.ProductNumber,
    COALESCE(
        C1.[Key],
        (SELECT TOP 1 C2.[Key]
         FROM TableName C2
         WHERE C2.RowNumber < C1.RowNumber
           AND C2.[Key] IS NOT NULL
           AND C2.Level < C1.Level
           -- 确保锚点行C2到当前行C1之间，没有Level≤C2的行（传播未终止）
           AND NOT EXISTS (
               SELECT 1
               FROM TableName C3
               WHERE C3.RowNumber > C2.RowNumber
                 AND C3.RowNumber < C1.RowNumber
                 AND C3.Level <= C2.Level
           )
         ORDER BY C2.RowNumber DESC
        )
    ) AS [Key]
FROM TableName C1
ORDER BY C1.RowNumber;

方案二：基于锚点范围的窗口函数方案 #

先预计算每个非NULL Key行的传播范围，再为每个行匹配对应的锚点，适合数据量大的场景：

WITH AnchorRows AS (
    -- 筛选所有非NULL Key的锚点行，并找到下一个锚点行的位置
    SELECT 
        *,
        LEAD(RowNumber) OVER(ORDER BY RowNumber) AS NextAnchorRow
    FROM TableName
    WHERE [Key] IS NOT NULL
),
AnchorRanges AS (
    -- 确定每个锚点行的传播覆盖范围：从当前行号到下一个锚点行的前一行
    SELECT
        RowNumber AS StartRow,
        ISNULL(NextAnchorRow - 1, (SELECT MAX(RowNumber) FROM TableName)) AS EndRow,
        [Key],
        Level AS AnchorLevel
    FROM AnchorRows
)
SELECT
    C1.RowNumber,
    C1.Level,
    C1.PartNumber,
    C1.ProductNumber,
    COALESCE(
        C1.[Key],
        -- 找到覆盖当前行且层级符合要求的最新锚点
        (SELECT TOP 1 AR.[Key]
         FROM AnchorRanges AR
         WHERE AR.StartRow <= C1.RowNumber
           AND AR.EndRow >= C1.RowNumber
           AND C1.Level > AR.AnchorLevel
         ORDER BY AR.StartRow DESC
        )
    ) AS [Key]
FROM TableName C1
ORDER BY C1.RowNumber;

方案说明 #

• 方案一逻辑直观，适合小数据量场景；
• 方案二通过预计算锚点范围，避免了多次相关子查询的开销，大数据量下性能更优；
• 两个方案均严格遵循规则：仅传播给层级更高的行，遇到同级或更低层级的行立即终止传播。

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