php - 仅获取 Group By 返回的最高值

Question

这是我的表格(简化版)的样子

agentID|amount
   1   |   5  
   2   |   2  
   3   |   4  
   3   |   2  
   1   |   3

现在如果我这样做

SELECT agentID, SUM(amount) AS totalAmount FROM myTable GROUP BY agentID

我会得到每个 agentID 的金额总和。但是，如果我只对返回的 sum(amount) 最大的 agentID 感兴趣怎么办？

尝试过类似的东西

SELECT agentID, MAX(SUM(amount) AS Bought) FROM myTable GROUP BY agentID

或

SELECT MAX(SELECT agentID, SUM(amount) AS Bought FROM myTable GROUP BY agentID) FROM myTable

但它返回语法错误:p

Answer 1

您可以在末尾添加ORDER BY

SELECT agentID, SUM(amount) AS totalAmount FROM myTable GROUP BY agentID ORDER BY SUM(amount) DESC LIMIT 1;