MySQL如何查询每个id分组对应的第二大count值

下面分两种常用场景提供可直接运行的SQL：

场景1：MySQL 8.0及以上版本（支持窗口函数） #

推荐使用窗口函数实现，逻辑清晰易维护：

WITH ranked_data AS (
    SELECT 
        id,
        `count`,
        -- 按id分组后对count倒序排名，相同count排名一致
        DENSE_RANK() OVER (PARTITION BY id ORDER BY `count` DESC) AS rank_num
    FROM 你的表名
)
SELECT id, `count`
FROM ranked_data
WHERE rank_num = 2;

如果需要相同count按不同行独立排名，把DENSE_RANK()替换为ROW_NUMBER()即可。

场景2：MySQL 5.x及更低版本（无窗口函数支持） #

可以用自关联+分组的方式实现：

SELECT 
    t1.id,
    MAX(t1.`count`) AS `count`
FROM 你的表名 t1
LEFT JOIN 你的表名 t2 
    ON t1.id = t2.id AND t1.`count` < t2.`count`
GROUP BY t1.id
-- 同id下比当前count大的去重后count数量为1，说明当前count就是第二大
HAVING COUNT(DISTINCT t2.`count`) = 1;

注意事项 #

• count是MySQL保留关键字，作为字段名使用时必须用反引号包裹，否则会触发语法错误
• 如果某个id下只有1条count记录，上述查询默认不会返回该id，如果需要返回这类id并把第二大count置为NULL，可以额外加逻辑处理

内容的提问来源于stack exchange，提问作者이재빈