使用T-SQL查询层级数据并获取聚合数据的高效方案

需求说明 #

查询不可修改的单表父子树形数据，每行需返回：

• 自身全部数据
• 友好展示的层级路径（如First Level > Second Level 1 > Third Level 1）
• 层级中的最顶层父节点（孤儿节点则返回自身）
• 最严格的有效期：取层级内所有节点的最大ValidFrom和最小ValidTo，NULL代表无限制

表结构 #

CREATE TABLE [dbo].[HiTest]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [Name] [varchar](100) NOT NULL,
    [ValidFrom] [datetime] NULL,
    [ValidTo] [datetime] NULL,

    CONSTRAINT [PK_HiTest] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

测试数据 #

SET IDENTITY_INSERT [dbo].[HiTest] ON 
GO

INSERT [dbo].[HiTest] ([Id], [ParentId], [Name], [ValidFrom], [ValidTo]) 
VALUES (1, NULL, N'First Level', NULL, NULL),
       (2, 1, N'Second Level 1', CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime)),
       (3, 1, N'Second Level 2', NULL, NULL),
       (4, 2, N'Third Level 1', CAST(N'2022-02-01T00:00:00.000' AS DateTime), CAST(N'2022-12-31T00:00:00.000' AS DateTime)),
       (5, 3, N'Third Level 2', CAST(N'2022-03-01T00:00:00.000' AS DateTime), CAST(N'2022-10-31T00:00:00.000' AS DateTime)),
       (6, 4, N'Fourth Level 1a', CAST(N'2022-01-01T00:00:00.000' AS DateTime), CAST(N'2022-09-30T00:00:00.000' AS DateTime)),
       (7, 4, N'Fourth Level 1b', NULL, CAST(N'2022-11-30T00:00:00.000' AS DateTime)),
       (8, 23, N'Orphaned Level', NULL, NULL)
GO

SET IDENTITY_INSERT [dbo].[HiTest] OFF

注：原测试数据存在重复ID=6的问题，已修正为ID=7的记录

预期结果 #

高效实现方案（适配12万行） #

1. 索引优化（关键前提） #

针对12万行规模，必须创建覆盖索引避免全表扫描，大幅提升递归效率：

CREATE NONCLUSTERED INDEX IX_HiTest_ParentId 
ON [dbo].[HiTest] (ParentId)
INCLUDE (Id, Name, ValidFrom, ValidTo);

2. 优化后的递归CTE查询 #

通过递归CTE从顶层/孤儿节点向下遍历，同时聚合路径、顶层父节点和有效期：

WITH TreeCTE AS (
    -- 锚点成员：顶层节点（ParentId为NULL）和孤儿节点（ParentId不存在于Id集合）
    SELECT 
        h.Id,
        h.ParentId,
        h.Name,
        h.ValidFrom,
        h.ValidTo,
        CAST(h.Name AS VARCHAR(MAX)) AS LevelPath,
        h.Id AS TopParentId,
        h.Name AS TopParentName,
        h.ValidFrom AS StrictValidFrom,
        h.ValidTo AS StrictValidTo
    FROM [dbo].[HiTest] h
    WHERE h.ParentId IS NULL 
       OR NOT EXISTS (SELECT 1 FROM [dbo].[HiTest] WHERE Id = h.ParentId)

    UNION ALL

    -- 递归成员：遍历子节点，聚合路径与有效期
    SELECT 
        c.Id,
        c.ParentId,
        c.Name,
        c.ValidFrom,
        c.ValidTo,
        CAST(p.LevelPath + ' > ' + c.Name AS VARCHAR(MAX)) AS LevelPath,
        p.TopParentId,
        p.TopParentName,
        -- 取最大ValidFrom（NULL视为无限制，保留非空值）
        CASE 
            WHEN p.StrictValidFrom IS NULL THEN c.ValidFrom
            WHEN c.ValidFrom IS NULL THEN p.StrictValidFrom
            ELSE IIF(p.StrictValidFrom > c.ValidFrom, p.StrictValidFrom, c.ValidFrom)
        END AS StrictValidFrom,
        -- 取最小ValidTo（NULL视为无限制，保留非空值）
        CASE 
            WHEN p.StrictValidTo IS NULL THEN c.ValidTo
            WHEN c.ValidTo IS NULL THEN p.StrictValidTo
            ELSE IIF(p.StrictValidTo < c.ValidTo, p.StrictValidTo, c.ValidTo)
        END AS StrictValidTo
    FROM [dbo].[HiTest] c
    INNER JOIN TreeCTE p ON c.ParentId = p.Id
)
SELECT * FROM TreeCTE
ORDER BY Id;

3. 性能说明 #

• 覆盖索引IX_HiTest_ParentId让递归过程无需回表查询，是12万行数据高效查询的核心
• 若树形结构深度超过100层（SQL Server默认递归限制），可添加OPTION (MAXRECURSION 0)取消限制，但需提前排查避免循环引用
• 该方案在常规树形结构下，12万行数据可在秒级完成查询

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