mysql - sql 上周总计

Question

寻找上周总计的查询

当天

SELECT Sum(total_cost)AS "total" 
FROM   purchases 
WHERE  datetime >= Curdate();

本周

SELECT SUM(total_cost)as "total" 
FROM purchases 
WHERE datetime >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK);

前一天

SELECT SUM(total_cost)AS "total"
FROM purchases
WHERE datetime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND datetime <= CURDATE();

寻找与此类似的查询来计算前一周。似乎无法正常工作。

Answer 1

也许你想要这个:-

SELECT SUM(total_cost)AS "total"
    FROM purchases
`datetime ` >= DATE_SUB(NOW(),INTERVAL 14 DAY) 
AND `datetime ` < DATE_SUB(NOW(),INTERVAL 7 DAY)