MYSQL 选择最大购买金额

Question

我有下表名为 Order

Orders Table
___________________________________________________
orderiD | userId | OrderType | Order_Date | Amount
________|________|___________|____________|________
1          1          0         12/12/2009    1
2          1          1         13/12/2009    2
3          1          1         14/12/2009    3
4          2          0         12/12/2009    4
5          2          1         16/12/2009    2
6          1          0         14/12/2009    5
7          2          1         17/12/2009    4
8          2          0         10/12/2010    2
___________________________________________________

我需要创建查询以返回具有最大购买总和的用户 ID。

我尝试了以下方法

Select MAX(GRP.sumAmmount), o.userId join
(Select SUM(o.Amount) as sum_ammount, o.userId as UID from Orders GROUP BY(o.userID)) as GRP on o.userId=GRP.UID GROUP BY(GRP.UID)

但我相信我遗漏了一些东西。

你能帮忙吗？

Answer 1

如果我对您的问题的理解正确，您希望返回具有最大购买 SUM(总计)的 UserID。所以上面的记录会导致:

UserID  Total Amount
  2         12

最简单的解决方案是:

SELECT UserID, SUM(AMOUNT) as TotalAmount
FROM Orders
GROUP BY UserID
ORDER BY TotalAmount DESC
LIMIT 1

如果我错了，我准备好进行编辑。 :-)
(PS:请添加您想要的结果)
谢谢。

更新 1

源自您的查询:

  Select  MAX(SUM(o.Amount)) as sum_ammount, 
          o.userId as UID 
    FROM  Orders o
GROUP BY  o.userID
ORDER BY  sum_ammount DESC
   LIMIT  1