能否使用递归CTE实现带累计向下传递选项关联的层级行查询

该需求完全可以通过递归CTE实现。现有查询的核心缺陷是未在递归过程中累积传递全链路上级选项，每次递归仅关联当前节点的直接父级绑定选项，因此无法获取祖父及以上层级的关联选项。

现有查询问题定位 #

原有递归分支的最后一段逻辑，仅通过peg.Parent = pego.GroupID匹配当前节点直接父级的绑定选项，没有把从根节点到上一层已经遍历到的所有选项顺着递归链路传递到当前节点，导致隔层的祖先选项（比如三级节点3需要获取的一级节点1的选项）无法向下传递，最终结果缺失跨层级的选项关联。

优化实现逻辑 #

调整递归CTE的锚点与递归分支结构，让选项沿着父子分组的关联链路自然向下传递：

• 锚点层：遍历对应数据集中的所有根节点（Parent为NULL的顶级分组），生成每个根节点的占位行（OptionID=0），同时关联读取根节点自身绑定的所有选项
• 递归层：
  • 承接上一层递归输出的所有记录，关联匹配当前层的子分组，直接将上一层携带的所有选项（占位0值、各级祖先绑定的选项）全部继承到子节点，无需重复回溯查询祖先表
  • 为每个子节点额外关联自身绑定的选项，追加到该层级的选项集合中
• 最终结果去重，消除递归传递过程中产生的重复选项行

适配原表结构的优化代码 #

DECLARE @EstimateID int = (SELECT MAX(ID) FROM dbo.Project_Estimate);

WITH egroup (Parent, ID, OptionID) AS
(
    -- 锚点：当前数据集下所有根分组的0值占位行
    SELECT Parent, ID, 0 AS OptionID
    FROM Project_Estimate_Group
    WHERE Estimate = @EstimateID 
      AND Parent IS NULL

    UNION ALL

    -- 锚点：根分组自身绑定的所有选项
    SELECT peg.Parent, peg.ID, pego.OptionID
    FROM Project_Estimate_Group peg
    LEFT JOIN Project_Estimate_Group_Option pego
        ON peg.ID = pego.GroupID
    WHERE peg.Estimate = @EstimateID 
      AND peg.Parent IS NULL

    UNION ALL

    -- 递归：继承上一层传递的所有选项（含0占位、全祖先绑定选项）
    SELECT child.Parent, child.ID, parent_layer.OptionID
    FROM egroup parent_layer
    JOIN Project_Estimate_Group child
        ON child.Parent = parent_layer.ID
        AND child.Estimate = @EstimateID

    UNION ALL

    -- 递归：追加当前子分组自身绑定的选项
    SELECT child.Parent, child.ID, pego.OptionID
    FROM egroup parent_layer
    JOIN Project_Estimate_Group child
        ON child.Parent = parent_layer.ID
        AND child.Estimate = @EstimateID
    LEFT JOIN Project_Estimate_Group_Option pego
        ON child.ID = pego.GroupID
    WHERE parent_layer.OptionID != 0 -- 避免重复生成0值占位行
)
SELECT DISTINCT Parent, ID, OptionID
FROM egroup
ORDER BY ID, OptionID

结果验证 #

使用提供的示例数据执行上述代码，输出与预期完全匹配：

• 每个分组固定生成一行OptionID=0的记录
• 每个分组自动继承从根节点到当前节点全链路所有祖先分组绑定的选项
• 无任何绑定选项的分组（如示例中ID=4的分组）仅生成0值占位行，无多余关联记录

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