MySQL如何查询分组后指定列值最高的对应整行记录

你写的SQL存在两个核心问题：

• 违反SQL分组查询的语法规范：使用GROUP BY做分组聚合时，SELECT子句中出现的非聚合字段，必须同时出现在GROUP BY的分组字段列表中。你的查询里id、stock两个字段既没有用聚合函数包裹，也没有加入分组字段，在标准SQL、MySQL 5.7及以上版本默认开启ONLY_FULL_GROUP_BY模式时会直接抛出语法错误。
• 即使是在旧版MySQL等未强制校验分组规则的环境中能运行，返回的id、stock值是分组内随机扫描到的第一条记录的值，不会和MAX(invested)计算出的最高金额对应，自然无法返回最高持仓的完整记录。

根据你使用的数据库版本不同，有两种常用的可靠实现方案：

方案1：窗口函数实现（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

用窗口函数按投资人分组、按投资金额倒序打排名，再取排名为1的记录即可，逻辑清晰性能好：

SELECT id, person, stock, invested
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY person ORDER BY invested DESC) AS invest_rank
    FROM person_investments
) t
WHERE invest_rank = 1;

补充：如果业务上存在同一个人多只股票投资金额并列最高的场景，需要返回所有并列最高的记录时，把ROW_NUMBER()替换为RANK()即可。

方案2：关联子查询实现（兼容所有数据库版本，无窗口函数支持时使用） #

先聚合算出每个投资人的最高投资金额，再通过关联条件匹配到对应的完整记录：

SELECT p1.*
FROM person_investments p1
JOIN (
    SELECT person, MAX(invested) AS max_invested
    FROM person_investments
    GROUP BY person
) p2
ON p1.person = p2.person AND p1.invested = p2.max_invested;

该写法天然会返回同一投资人所有投资金额等于最高值的并列记录，不需要额外调整函数。

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