MySQL - 需要找到 MAX 的 SUM 查询

1005 发表于 Dev

1005

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

我的餐桌预订缩短如下：

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

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

希德斯

一种解决方案是将HAVING子句与相关子查询一起使用，该子查询获取max所有之间的值sums并将行限制为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仅active预订，您可以采用以下方法之一：

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)CASE WHEN ... END在SUM()方法内部使用：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。