如何从tableX表的每个分类中获取评分最高的条目？

嘿，针对你要获取每个category_id分类下rating评分最高记录的需求，我来给你几个更高效的解决方案——你原来那种循环查询的方式虽然能实现，但当数据量变大或者分类很多时，会产生多次数据库请求，效率很低，单次SQL查询的方式显然更优。

方法一：窗口函数（MySQL 8.0+ 推荐） #

如果你的MySQL版本是8.0及以上，窗口函数是最简洁直观的方案，还能灵活处理同分类下多条最高评分的记录：

场景1：返回所有最高评分的记录 #

如果同一个分类下有多个记录的rating都是最高分，想要全部返回，用RANK()函数：

SELECT id, rating, category_id
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY category_id ORDER BY rating DESC) AS ranking
    FROM tableX
) ranked_data
WHERE ranking = 1;

针对你的示例数据，这个查询会返回category_id=1下所有rating=10.0的记录（id:1475、1479、1491），以及其他分类的最高评分记录。

场景2：每个分类只返回一条最高评分记录 #

如果不管同分类下有多少条最高评分记录，只需要其中一条（比如取id最大的），用ROW_NUMBER()函数：

SELECT id, rating, category_id
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY rating DESC, id DESC) AS row_num
    FROM tableX
) numbered_data
WHERE row_num = 1;

这里额外加了id DESC排序，确保同评分下优先取id更大的记录，避免结果随机。

方法二：子查询关联法（兼容低版本MySQL） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用子查询先找出每个分类的最高评分，再关联原表获取对应记录：

SELECT t1.*
FROM tableX t1
INNER JOIN (
    SELECT category_id, MAX(rating) AS max_rating
    FROM tableX
    GROUP BY category_id
) t2 
ON t1.category_id = t2.category_id AND t1.rating = t2.max_rating;

这个方法和RANK()窗口函数的效果一致，会返回所有最高评分的记录。

性能优化：添加联合索引 #

你已经给category_id加了索引，但为了让上面的查询更快，建议创建联合索引：

ALTER TABLE tableX ADD INDEX idx_category_rating (category_id, rating DESC);

这个索引可以让数据库直接按分类分组并排序，不用全表扫描，大幅提升查询效率。

对比你原来的方案 #

你原来的循环查询方式（先查所有分类，再逐个查最高记录），在分类数量多的时候会产生大量数据库请求，不仅耗时，还会增加数据库负载。上面的单次查询方案只需要一次请求，性能优势非常明显。

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