MySQL - 查找分组总和的最大值(无限制)

Question

我想获取总金额最大的用户的user_id和总金额。我无法使用 LIMIT，因为这只会返回 1 条记录(多个用户的总金额可能相同)

这是我的数据架构和一些记录

CREATE TABLE transactions (
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  user_id BIGINT(20) NOT NULL, 
  amount FLOAT NOT NULL, PRIMARY KEY (id)
);

INSERT INTO transactions (user_id, amount) VALUES 
(1, 1000),
(1, 1000),
(1, 1000),
(2, 2000),
(2, 1000),
(3, 1000);

这是预期的结果。

+---------+------+
| user_id | sum  |
+---------+------+
|       1 | 3000 |
|       2 | 3000 |
+---------+------+

我可以使用下面的sql得到上面的结果。但是，我不知道是否有更好的方法。是否有必要重复同一个子查询两次？谢谢。

SELECT T1.user_id, T1.sum
FROM (
  SELECT user_id, SUM(amount) as sum
  FROM transactions
  GROUP BY user_id
) T1
WHERE T1.sum = (
  SELECT MAX(T2.sum)
  FROM (
    SELECT user_id, SUM(amount) as sum
    FROM transactions
    GROUP BY user_id
  ) T2
)
GROUP BY T1.user_id;

Answer 1

那么您可以将查询简化为

SELECT user_id, SUM(amount) as sum
FROM transactions
GROUP BY user_id
HAVING SUM(amount) = (      
  SELECT SUM(amount) as sum
  FROM transactions
  GROUP BY user_id
  ORDER BY SUM(amount) DESC 
  LIMIT 1
  )