SQL Server自关联表（PK/FK同表）报表查询实现求助

针对你需要生成的报表需求，递归CTE是处理这种自关联层级数据的最佳方案，能清晰实现Billing和TopLevel字段的规则逻辑。以下是符合预期的实现代码：

完整实现代码 #

WITH CompanyWithChildren AS (
    -- 标记每个节点是否存在子节点，用于计算TopLevel
    SELECT 
        Id,
        Name,
        ParentId,
        Provider,
        EXISTS(SELECT 1 FROM Company c2 WHERE c2.ParentId = c1.Id) AS HasChildren
    FROM Company c1
),
CompanyHierarchy AS (
    -- 锚点成员：处理所有顶级节点（ParentId为NULL）
    SELECT 
        Id,
        Name,
        ParentId,
        Provider,
        -- 计算Billing：有Provider用Provider，无则用自身Name
        CASE WHEN Provider IS NOT NULL THEN Provider ELSE Name END AS Billing,
        -- 计算TopLevel：有子节点的顶级节点显示自身Name，否则为NULL
        CASE WHEN HasChildren = 1 THEN Name ELSE NULL END AS TopLevel
    FROM CompanyWithChildren
    WHERE ParentId IS NULL

    UNION ALL

    -- 递归成员：处理子节点，继承父节点的Billing和TopLevel
    SELECT 
        c.Id,
        c.Name,
        c.ParentId,
        c.Provider,
        -- 子节点Billing：优先用自身Provider，否则继承父节点Billing
        CASE WHEN c.Provider IS NOT NULL THEN c.Provider ELSE ch.Billing END AS Billing,
        -- 子节点直接继承父节点的TopLevel
        ch.TopLevel
    FROM Company c
    INNER JOIN CompanyHierarchy ch ON c.ParentId = ch.Id
)

-- 最终查询输出报表字段
SELECT 
    Billing,
    Provider,
    TopLevel,
    Name
FROM CompanyHierarchy
ORDER BY Id;

代码逻辑说明 #

1. CompanyWithChildren CTE：提前标记每个节点是否为有子节点的顶级节点，为TopLevel字段的计算提供判断依据
2. 锚点成员：直接处理所有顶级节点：
   • Billing字段严格按照规则：存在Provider则用Provider，否则用节点自身名称
   • TopLevel字段：仅当顶级节点存在子节点时，显示自身名称，其余情况为NULL
3. 递归成员：通过自关联处理所有子节点：
   • Billing字段优先使用子节点自身的Provider，否则继承父节点的Billing值
   • TopLevel字段直接继承父节点的取值，保证父子节点的TopLevel一致
4. 最终查询：从CTE中取出所需字段，按Id排序后得到你需要的报表格式

关键场景验证 #

• 有Provider的顶级节点（如Name1、Name2）：Billing取Provider，TopLevel仅在节点有子节点时显示自身名称（如Name2）
• 无Provider的顶级节点（如Name6、Name7）：Billing取自身名称，TopLevel仅在节点有子节点时显示自身名称（如Name7）
• 子节点（如Name3、Name8）：Billing继承父节点的Billing值，TopLevel继承父节点的TopLevel值

学习建议 #

• 递归CTE是处理SQL层级数据的核心工具，重点掌握锚点成员定义起始数据、递归成员关联层级数据、UNION ALL连接两部分的基础结构
• 自关联表的处理中，EXISTS子查询是高效判断节点是否存在子节点的方式，比COUNT(*)更节省性能
• CASE表达式的多分支逻辑是实现业务规则字段的常用手段，可嵌套使用处理复杂条件

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