MySQL 日期范围

Question

我有一个自动查询，可以计算每周的搜索次数，目前它仅显示搜索发生的月份，我希望它显示它所显示的相应周的日期范围数据，即 30/09/13 - 07/10/13

我的查询:

SELECT COUNT(*) AS `count`,
    `region`,
    MONTHNAME(`date`) as `month`
FROM stores.stats
WHERE `date` > DATE_ADD(DATE (NOW()), INTERVAL - 1 WEEK)
    AND `date` < DATE (NOW())
GROUP BY `region`,
    MONTH(`date`);

Answer 1

只需将范围添加到您的选择列表中，如下所示:

SELECT COUNT(*) AS `count`,
    `region`,
    MONTHNAME(`date`) as `month`,
    DATE_ADD(DATE (NOW()), INTERVAL - 1 WEEK) as `range start`,
    DATE (NOW()) as `range stop`
FROM stores.stats
WHERE `date` > DATE_ADD(DATE (NOW()), INTERVAL - 1 WEEK)
    AND `date` < DATE (NOW())
GROUP BY `region`,
    MONTH(`date`)