我想每天在商店中进行最后30天的购买,然后返回最后30天的以下数据,例如
2017/04/01
purchases: 30
total: 900.01
2017/04/02
purchases: 30
total: 900.01
等等。到目前为止,我都不知道如何进行这种查询并提出以下想法
SELECT COALESCE(SUM(purchases.price)/1000,0) AS all_purchases,
min(purchases.time) AS start_interval, max(purchases.time) AS end_interval
FROM purchases
WHERE purchases.time::date >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND WHERE purchases.time::date <= DATE_SUB(NOW())
ORDER BY start_interval DESC
但这不是它的工作方式
您应该使用eg的组:
SELECT date(purchases.time) as my_date , count(*) as my_count,
COALESCE(SUM(purchases.price)/1000,0) AS all_purchases
FROM purchases
WHERE date(purchases.time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND date(purchases.time) <= CURDATE()
GROUP BY date(purchases.time)
ORDER BY my_date DESC
(您应该只使用一次。