如何编写SQL查询获取每个指定ID对应最多5条记录及总条数

云原生阿岚

2026-9-29

按评论ID分组取最新TOP 5回复并返回总回复数SQL实现

通用方案（支持MySQL8.0+、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的数据库）

使用窗口函数可以一次扫描完成分组排序、总计数统计，逻辑清晰性能优秀：

WITH ranked_replies AS (
    SELECT 
        ReplyId,
        ReplyContent,
        CommentId,
        PublishDate,
        -- 按评论ID分组，组内按发布时间倒序排名
        ROW_NUMBER() OVER (PARTITION BY CommentId ORDER BY PublishDate DESC) AS row_num,
        -- 统计每个评论对应的总回复数
        COUNT(*) OVER (PARTITION BY CommentId) AS TotalCount
    FROM Replies
    WHERE CommentId IN (1,2,3,4,5)
)
SELECT ReplyId, ReplyContent, CommentId, TotalCount
FROM ranked_replies
WHERE row_num <= 5
ORDER BY CommentId, PublishDate DESC;

兼容MySQL5.x等不支持窗口函数的数据库方案

使用关联子查询实现同等效果：

SELECT 
    R1.ReplyId,
    R1.ReplyContent,
    R1.CommentId,
    -- 子查询统计当前评论总回复数
    (SELECT COUNT(*) FROM Replies R2 WHERE R2.CommentId = R1.CommentId) AS TotalCount
FROM Replies R1
WHERE R1.CommentId IN (1,2,3,4,5)
-- 筛选组内发布时间排在前5的记录
AND (
    SELECT COUNT(*) 
    FROM Replies R3 
    WHERE R3.CommentId = R1.CommentId 
    AND R3.PublishDate > R1.PublishDate
) < 5
ORDER BY R1.CommentId, R1.PublishDate DESC;