mysql - 获取表中每个组的最大值

Question

所以，我有一张类似这样的 table ......

 person    |   account    |   accountbalance
--------------------------------------------
  1               a             100
  1               b             250
  1               c             283
  2               a             25
  2               b             199
  3               a             65

对于每个人，我需要找到余额最高的帐户。我现在正在做这个:

SELECT person, account, accountbalance FROM mytable
AND accountbalance=  
    (SELECT MAX(accountbalance) FROM mytable);

但这只会返回其中的一个顶级帐户，而不是每个人。

Answer 1

一种方法是计算派生表中的最大值并与之连接:

SELECT mytable.person, account, accountbalance 
FROM mytable
JOIN (
  SELECT person, MAX(accountbalance) MaxAccountBalance 
  FROM mytable
  GROUP BY person
) t ON mytable.person = t.person
   AND mytable.accountbalance = t.MaxAccountBalance;

或者您可以在 where 子句中执行相关子查询(这就是您几乎所做的 - 您只是错过了必要的相关性):

SELECT person, account, accountbalance 
FROM mytable m1
WHERE accountbalance = (SELECT MAX(accountbalance) FROM mytable WHERE person = m1.person);

Sample SQL Fiddle