mysql - MySQL 查询中的自动日期更改

Question

我最近修改了每周运行的查询:

新查询

SELECT COUNT(*) as `count`, 
       `lang`, DATE(NOW()) as `week_ending` 
  FROM mytable.data 
 WHERE `date` > "2014-01-13" 
   AND `date` < "2014-01-20" 
 GROUP BY `lang`;

带有先前日期示例的旧查询

 SELECT COUNT(*) as `count`, 
       `region`, DATE(NOW()) as `week_ending` 
   FROM mytable.data 
  WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) 
    AND `date` < DATE(NOW()) 
  GROUP BY `region`, DATE(NOW());

正如您所看到的，我已自动将查询日期设置为每周更改，但是使用新查询时，它没有像以前那样给我正确的结果？有没有其他方法可以让日期每周更改一次，以便我可以尝试该方法？

Answer 1

新旧之间的区别是旧查询使用当前日期 - 7 天意味着 2014-01-10 和 2014-01-17...

据我所知，您正在使用不同的 group by 子句

SELECT COUNT(*) as `count`, `lang`, DATE(NOW()) as `week_ending` 
FROM mytable.data 
WHERE `date` > "2014-01-10" 
AND `date` < "2014-01-17" GROUP BY `lang`;