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

6kkfgxo0  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(334)

我想拿到 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;
eyh26e7m

eyh26e7m1#

你可以把你的查询简化为

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
  )

相关问题