MySQL：查找分组MAX()值对应的其他列行值

嘿，我来帮你搞定这个需求！你现在需要从关联的电影、评分、评论者数据里，找出每部电影评分最高的那条完整记录（包含电影名、评分、评论者名字）对吧？下面给你两种实用的解决方法：

方法一：子查询+关联（兼容所有MySQL版本） #

这种方法先通过子查询算出每部电影的最高评分，再把这个结果和原表关联，筛选出匹配的记录。如果有多条记录是同一部电影的最高分，所有符合的都会被保留。

SELECT m.title, ra.stars, re.name
FROM Movie m
JOIN Rating ra ON m.mid = ra.mid
JOIN Reviewer re ON ra.rid = re.rid
-- 子查询获取每部电影的最高评分
JOIN (
    SELECT mid, MAX(stars) AS max_stars
    FROM Rating
    GROUP BY mid
) AS max_ratings 
    ON m.mid = max_ratings.mid 
    AND ra.stars = max_ratings.max_stars
ORDER BY m.title;

逻辑说明： #

1. 子查询max_ratings按电影ID（mid）分组，计算出每部电影的最高评分max_stars；
2. 将原三表关联的结果和这个子查询做连接，同时匹配电影ID和评分等于最高评分的条件；
3. 最后按电影名排序，得到每部电影评分最高的完整记录。

方法二：窗口函数（MySQL 8.0及以上版本适用） #

如果你的MySQL版本是8.0或更高，用窗口函数会更简洁直观。这里用RANK()函数给每部电影的评分按降序排名，然后筛选排名为1的记录。

SELECT title, stars, name
FROM (
    SELECT 
        m.title, 
        ra.stars, 
        re.name,
        -- 按电影ID分组，每组内按评分降序排名
        RANK() OVER (PARTITION BY m.mid ORDER BY ra.stars DESC) AS rating_rank
    FROM Movie m
    JOIN Rating ra ON m.mid = ra.mid
    JOIN Reviewer re ON ra.rid = re.rid
) AS ranked_ratings
WHERE rating_rank = 1
ORDER BY title;

逻辑说明： #

1. 内层查询给每条记录加上一个rating_rank字段：按电影ID分组（PARTITION BY m.mid），每组内按评分从高到低排序排名；
2. 外层查询筛选出排名为1的记录，也就是每部电影评分最高的记录；
3. 如果遇到同一部电影有多个相同最高分的记录，RANK()会让它们都排第1；要是只想取其中一条（比如随机选），可以把RANK()换成ROW_NUMBER()。

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