mysql - 选择该期间内首次付款的付款金额

Question

我有付款表“user_id - 创建 - 价格”。我需要计算 3 月 14 日至 16 日期间支付的所有付款的总和，其中第一笔付款是在同一时期内支付的

我找到的最好的解决方案是

 SELECT user_id, SUM(price/100) FROM payment WHERE
 (DATE(created) BETWEEN '2014-03-14' AND '2014-03-16')
 AND (MIN(created) BETWEEN '2014-03-14 00:00:01' AND '2014-03-16 23:59:59')
 GROUP BY user_id

但我收到“无效使用组功能”

更新:这个请求解决了我的问题

SELECT p.user_id, SUM(p.price/100) FROM payment p WHERE
(DATE(created) BETWEEN '2014-03-14' AND '2014-03-16')
AND (SELECT MIN(DATE(created)) FROM payment WHERE user_id = p.user_id) BETWEEN '2014-03-14' AND '2014-03-16'
GROUP BY p.user_id

Answer 1

您不能在 where 子句中使用 MIN() 函数，您需要使用 HAVING 来过滤聚合函数，您可以按如下方式重写查询

 SELECT user_id, SUM(price/100) 
 FROM payment 
 WHERE
 created BETWEEN '2014-03-14 00:00:00' AND '2014-03-16 23:59:59'
 GROUP BY user_id
 HAVING MIN(created) BETWEEN '2014-03-14 00:00:00' AND '2014-03-16 23:59:59'