如何简便查询含最大值的行？以电影参与人数统计为例

嘿，我完全懂你现在的困扰——要揪出参与人数最多的电影，还得保留电影的关联信息，但之前写的SQL结构太复杂绕人。其实有几种简洁又高效的方法能搞定这个需求，我给你整理几个常用方案：

方案1：用窗口函数（首推，清晰易维护） #

窗口函数是处理这种「找最大值对应行」场景的绝佳工具，不用嵌套多层子查询就能搞定：

SELECT m.*, p.count_person
FROM (
    SELECT movie_id, COUNT(*) AS count_person,
           RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM participation
    GROUP BY movie_id
) p
JOIN movie m ON p.movie_id = m.id
WHERE p.rnk = 1;

简单解释下：

• 先在participation表分组统计每部电影的参与人数，用RANK()窗口函数给每个分组按人数降序排名
• 排名为1的就是参与人数最多的（如果有多部电影并列第一，RANK()会把它们都列出来；要是只想取其中一部，换成ROW_NUMBER()就行）
• 最后关联movie表，直接拿到电影的完整信息

方案2：子查询先拿最大人数再关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如老版本MySQL），可以用子查询先找出最大参与人数，再反向关联：

SELECT m.*, COUNT(*) AS count_person
FROM participation p
JOIN movie m ON p.movie_id = m.id
GROUP BY p.movie_id, m.id
HAVING COUNT(*) = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM participation
        GROUP BY movie_id
    ) t
);

逻辑很直白：

• 最内层子统计每部电影的参与人数，中间层找出这些人数里的最大值
• 外层分组统计后，用HAVING筛选出人数等于最大值的电影，同时关联movie表拿详情

方案3：用LIMIT快速取单条结果（仅适用于唯一最大值场景） #

如果你能确定只有一部电影是参与人数最多的，这个方法最简洁：

SELECT m.*, COUNT(*) AS count_person
FROM participation p
JOIN movie m ON p.movie_id = m.id
GROUP BY p.movie_id, m.id
ORDER BY COUNT(*) DESC
LIMIT 1;

注意哦：如果有多个电影并列第一，这个写法只会返回其中一部，适合你确定最大值唯一的场景。

小提醒 #

不同数据库的语法细节可能有差异，比如MySQL开启ONLY_FULL_GROUP_BY模式时，分组字段要包含所有非聚合的movie表字段，或者用聚合函数处理这些字段。

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