如何高效查询最近有活动的10个帖子及最新评论时间

问题背景 #

现有posts.Comment表，核心字段为PostId（帖子ID）和Timestamp（评论时间），已创建索引IX_Timestamp_PostId (Timestamp DESC, PostId)和IX_PostId_Timestamp (PostId, Timestamp DESC)。原GROUP BY查询会触发全表扫描，需找到更高效的简洁实现。

简洁高效的解决方案 #

方案一：CTE+关联查询（推荐，可读性强）

WITH RecentUniquePosts AS (
    -- 利用索引快速抓取10个最近有评论的唯一帖子ID
    SELECT DISTINCT TOP 10 PostId
    FROM posts.Comment
    ORDER BY Timestamp DESC, PostId
)
SELECT 
    rup.PostId,
    MAX(c.Timestamp) AS LatestCommentTime
FROM RecentUniquePosts rup
JOIN posts.Comment c ON rup.PostId = c.PostId
GROUP BY rup.PostId
ORDER BY LatestCommentTime DESC, rup.PostId

方案二：窗口函数紧凑写法

SELECT DISTINCT TOP 10
    PostId,
    FIRST_VALUE(Timestamp) OVER (
        PARTITION BY PostId 
        ORDER BY Timestamp DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LatestCommentTime
FROM posts.Comment
ORDER BY Timestamp DESC, PostId

性能说明 #

• 两种方案均会利用IX_Timestamp_PostId索引，通过Flow Distinct运算符在找到10个唯一PostId后立即停止读取，完全避免全表扫描。
• 方案一中的关联查询会借助IX_PostId_Timestamp索引，快速定位每个PostId的所有评论并获取最新时间，无需遍历全表。

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