在分层表中查找最后一条记录的最有效方法

Question

我有一个存储论坛帖子的SQL表。每条记录都有一个ParentID，它是另一条记录的ID。如果它为null，则意味着它是一个主要论坛线程，因此没有父记录。每个帖子都可以有多个级别的子帖子。我正在寻找一个函数，它可以有效地递归子记录，以找到最后创建的记录（每个记录都有一个"created"字段）。

我确实有一个存储过程，它列出了属于特定父级的记录，但它非常占用CPU，在列出主论坛线程和显示最后回复日期时无法使用。

如果有帮助的话，下面是表格的基本结构：

CREATE TABLE [dbo].[ForumThreads](
    [ForumThreadID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ForumID] [numeric](18, 0) NULL,
    [ParentID] [numeric](18, 0) NULL,
    [PostSubject] [nvarchar](500) NULL,
    [PostBody] [nvarchar](max) NULL,
    [Created] [datetime] NULL,
    [CreatedBy] [numeric](18, 0) NULL
);

Answer 1

我仔细阅读了你的答案，并删除了我的第一个答案，因为它是错误的。这是一个SQL FIDDLE，其解决方案包括一个设置和一个递归CTE查询，该查询使用"TopLevelForumID"字段对所有相关论坛进行分组。这个查询应该是高效的，但是根据您的表统计信息，您可以添加一个INDEX来提高响应速度。

SQL Fiddle

增加了标量值函数，可以这样调用：

-- How yo use this Scalar-valued function
--SELECT [ForumThreadID]
--      ,[ForumID]
--      ,[ParentID]
--      ,[PostSubject]
--      ,[PostBody]
--      ,[Created]
--      ,[CreatedBy]
--      ,[dbo].[fn_getLastThread]([ForumID]) AS [LastThread]
--  FROM [dbo].[ForumThreads]
CREATE FUNCTION [dbo].[fn_getLastThread]
(
    @ParentForumID INT
)
RETURNS INT
AS
BEGIN
    DECLARE @Ret INT;
    -- recursive CTE query
    WITH hierarchy AS (
            -- select the parent row
            SELECT  [ForumID],[ParentID],[Created]
            FROM    ForumThreads
            WHERE   [ForumID]=@ParentForumID
            UNION ALL               
            SELECT  A.[ForumID],A.[ParentID],A.[Created]
            FROM    ForumThreads A
            INNER JOIN hierarchy H ON H.[ForumID] = A.[ParentID]
    )
    SELECT      TOP 1 @Ret = [ForumID]
    FROM        hierarchy
    ORDER BY    [Created] DESC
    OPTION      (MAXRECURSION 50); -- specify recursion limit (default is 100 if not specified)
    RETURN @Ret;
END