MySQL - 需要找到 SUM 查询的最大值

Question

我需要找到支付预订费用最多的用户，如果有 2 个或更多用户具有相同的金额，则需要显示所有用户，所以我需要 MAX of SUM。

我的餐 table 预订缩短如下:

reservation_id, user_id, performance_id, amount_to_pay, date

所以我有这个代码

SELECT user_id, SUM(amount_to_pay) FROM reservation GROUP BY user_id

我得到了

User 1 - 9000
User 2 - 9000
User 3 - 5000

需要向用户 1 和用户 2 显示 9000。

Answer 1

一种解决方案是将 HAVING 子句与相关子查询结合使用，该子查询获取所有总和之间的max值，并将行限制为SUM(amount_to_pay) 等于 max_value 的人。

SELECT
   user_id,
   SUM(amount_to_pay) AS total
FROM
   reservation AS r
GROUP BY
   user_id
HAVING
   total = (SELECT SUM(amount_to_pay) AS tot
            FROM reservation
            GROUP BY user_id
            ORDER BY tot DESC LIMIT 1)

在线示例:DB-Fiddle

<小时/>

更新帖子评论:

对于sum仅事件预订，您可以采取以下方法之一:

A)添加外查询和子查询的限制:

SELECT
   user_id,
   SUM(amount_to_pay) AS total
FROM
   reservation AS r
WHERE
   status = "active"
GROUP BY
   user_id
HAVING
   total = (SELECT SUM(amount_to_pay) AS tot
            FROM reservation
            WHERE status = "active"
            GROUP BY user_id
            ORDER BY tot DESC LIMIT 1)

B) 在 SUM() 方法中使用 CASE WHEN ... END:

SELECT
   user_id,
   SUM(CASE WHEN status = "active" THEN amount_to_pay END) AS total
FROM
   reservation AS r
GROUP BY
   user_id
HAVING
   total = (SELECT SUM(CASE WHEN status = "active" THEN amount_to_pay END) AS tot
            FROM reservation
            GROUP BY user_id
            ORDER BY tot DESC LIMIT 1)

在线示例:DB-Fiddle