MySQL - 按月的最大摘要

Question

我有这个实体:

payments(idPayment, idGroup, price, paymentDate)

我想找出最大月份(可记录的月份)。我有最高价，但月份不对。

Select Max(x.price), x.month, x.year from(
   SELECT SUM(price) as price, Year(paymensDate) as year, Month(paymentDate) as month FROM `payments` 
   where idGroup=27 group by Year(paymentDate), Month(paymentDate)
) as x

多谢指教

Answer 1

您可以使用ORDER BY 和LIMIT 来选择价格最高的行

SELECT SUM(price) as price, 
       YEAR(paymensDate) as year, 
       MONTH(paymentDate) as month 
FROM `payments` 
WHERE idGroup=27 
GROUP BY YEAR(paymentDate), 
         MONTH(paymentDate)
ORDER BY price DESC 
LIMIT 1

MAX 将为提供的列提供最大值，但不保证列的其他值返回与 max 选取的行相同的值