如何在SQL Server中为给定记录构建符合要求的管理层级？

我来帮你搞定这个层级数据的格式化问题！你之前尝试递归CTE没得到正确结果，那咱们换个更直观的思路，用窗口函数来实现你要的输出格式。先理清楚需求和数据：

输入数据 #

输出规则 #

• New Leaf ID：
  • Sub ID为NULL时，值为1
  • 名称以"Page"开头的行（也就是你说的Page行），值为2
  • 其他所有行值为3
• Page列：从Page行开始，后续的非Page行都继承最近的前置Page行的名称作为自己的Page值；Page行自身的Page值就是它的名称。

预期输出 #

解决方案（SQL代码） #

我们可以用窗口函数来分组并填充Page列，同时用CASE语句生成New Leaf ID。假设你的表名为page_hierarchy，代码如下：

WITH page_groups AS (
    SELECT 
        Id,
        "Sub ID",
        Name,
        -- 给每个Page行及其子项分配同一个组ID
        SUM(CASE WHEN Name LIKE 'Page%' THEN 1 ELSE 0 END) OVER (ORDER BY Id) AS page_group
    FROM page_hierarchy
)
SELECT 
    pg.Id,
    pg."Sub ID",
    pg.Name,
    -- 填充Page列：Page行用自己的名称，子项用组内最近的Page名称
    CASE 
        WHEN pg.Name LIKE 'Page%' THEN pg.Name
        ELSE (SELECT Name FROM page_hierarchy ph WHERE ph.Name LIKE 'Page%' AND (SELECT SUM(CASE WHEN Name LIKE 'Page%' THEN 1 ELSE 0 END) OVER (ORDER BY Id) FROM page_hierarchy WHERE Id=ph.Id) = pg.page_group)
    END AS Page,
    -- 生成New Leaf ID
    CASE
        WHEN pg."Sub ID" IS NULL THEN 1
        WHEN pg.Name LIKE 'Page%' THEN 2
        ELSE 3
    END AS "New Leaf ID"
FROM page_groups pg
ORDER BY 
    -- 把Page Reference放在最前面
    CASE WHEN pg.Name = 'Page Reference' THEN 0 ELSE 1 END,
    pg.page_group,
    -- 每个组内先显示Page行，再显示子项
    CASE WHEN pg.Name LIKE 'Page%' THEN 0 ELSE 1 END,
    pg.Id;

如果你用的是支持IGNORE NULLS的数据库（比如PostgreSQL、Oracle），可以简化成更高效的版本，不用子查询：

SELECT 
    Id,
    "Sub ID",
    Name,
    LAST_VALUE(CASE WHEN Name LIKE 'Page%' THEN Name END) OVER (
        ORDER BY Id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
        IGNORE NULLS
    ) AS Page,
    CASE
        WHEN "Sub ID" IS NULL THEN 1
        WHEN Name LIKE 'Page%' THEN 2
        ELSE 3
    END AS "New Leaf ID"
FROM page_hierarchy
ORDER BY 
    CASE WHEN Name = 'Page Reference' THEN 0 ELSE 1 END,
    SUM(CASE WHEN Name LIKE 'Page%' THEN 1 ELSE 0 END) OVER (ORDER BY Id),
    CASE WHEN Name LIKE 'Page%' THEN 0 ELSE 1 END,
    Id;

代码解释 #

1. 分组逻辑：用SUM() OVER (ORDER BY Id)给每个Page行和它后面的子项标记同一个组ID，这样就能把它们归为一组处理。
2. Page列填充：要么直接取当前行的名称（如果是Page行），要么取组内最近的Page行名称；支持IGNORE NULLS的数据库可以直接用LAST_VALUE跳过空值，自动取最近的有效Page名称。
3. New Leaf ID生成：通过CASE语句直接匹配规则，判断Sub ID是否为空、是否是Page行来赋值。
4. 排序控制：确保输出顺序和预期一致，把Page Reference放在最前面，每个组内先显示Page行，再显示子项。

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