mySQL 查询两个日期之间的总和问题

Question

我有下一个名为费用_部分_付款的表。

我的查询是下一个，我需要 amount_paid 的总和作为total_paid:

SELECT id_partial_payment, epp.id_billing, epp.id_user, epp.month_of_payment,  
            COALESCE(SUM(epp.amount_paid),0) AS total_paid,
            (be.total - amount_paid) AS pending_total , be.total as total

            FROM expenses_partial_payment epp 
            INNER JOIN BillingExpenses be  ON epp.id_billing = be.id_billing
            WHERE epp.id_user = 23 

但是当我添加子句 where

WHERE epp.id_user = 23 AND  (Month_of_payment BETWEEN '2015-01-01' AND LAST_DAY('2016-01-01'))

我的total_piad有错误，查询仅返回最后一行(300)并且不对amount_paid求和，正确的总和必须是501.00

我该怎么做才能获得正确的总和，添加过滤月份付款的子句

(month_of_payment BETWEEN '2015-01-01' AND LAST_DAY('2016-01-01'))

Answer 1

“2015-01-01”与 LAST_DAY(“2016-01-01”) 之间的付款月份

last day() 返回月份而不是年份的最后一天。因此它不会提取大于一月份的数据。