MySQL 8.0 LIMIT取TOP5用户时如何包含所有同票数的并列用户

需求背景 #

原有查询select * from user_votes order by votes desc limit 5只能返回固定5条数据，若第5名与后续用户票数相同会出现遗漏，需要返回所有与第5名票数相等的用户，最低返回5行。

实现方案 #

方案1：子查询匹配第5名票数（写法简洁，性能最优） #

SELECT *
FROM user_votes
WHERE votes >= (
    SELECT votes
    FROM user_votes
    ORDER BY votes DESC
    LIMIT 4,1
)
ORDER BY votes DESC;

说明：

• 内层查询通过LIMIT 4,1拿到排序后第5行的票数（offset从0开始计数）
• 外层查询筛选所有票数大于等于该值的用户，自动包含所有并列第5名的记录
• 给votes字段加索引后，查询效率几乎和原LIMIT写法一致
• 如需兼容表数据不足5行的场景，可将条件调整为WHERE votes >= COALESCE((SELECT votes FROM user_votes ORDER BY votes DESC LIMIT 4,1), 0)，总数据量小于5时会返回全部数据

方案2：CTE+窗口函数（可读性高，便于扩展复杂逻辑） #

WITH ranked_users AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY votes DESC) AS row_num
    FROM user_votes
)
SELECT *
FROM user_votes
WHERE votes >= (SELECT votes FROM ranked_users WHERE row_num = 5)
ORDER BY votes DESC;

说明：

• 先用CTE给所有用户按票数倒序生成行号
• 匹配行号为5的用户票数作为阈值，筛选所有符合要求的记录
• 适合后续需要叠加其他排名统计逻辑的场景

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