mysql - 按列分组时选择零计数

Question

我有一个工作表，我正在尝试计算不同时间范围内的工作数。我当前的查询如下所示:

SELECT COUNT(*) AS 'count', 
       WEEK(j.created_at) AS 'week',
       MONTH(j.created_at) AS 'month', 
       YEAR(j.created_at) AS 'year', 
       DATE_FORMAT(j.created_at, '%y') AS 'short_year'
FROM jobs j WHERE j.state <> 'draft' 
            AND created_at > '2010-06-21'
            AND created_at < '2010-08-01'
GROUP BY WEEK(j.created_at)
ORDER BY WEEK(j.created_at)

要更改我的时间范围，我只需将 GROUP BY 从 WEEK 更改为 MONTH，然后我会按月而不是按周进行计数。

问题是我在 0 个作业的数周内没有得到空行。上面查询的结果集是:

count week  month  year short_year
    3   25      6  2010         10
    2   26      6  2010         10
    2   27      7  2010         10 
    1   28      7  2010         10
    3   30      7  2010         10

您会注意到第 29 周没有数据，这应该是 count(0) 的一行。有什么方法可以获取 0 计数行，同时保持在 WEEK 和 MONTH 之间更改分组的灵 active ？

Answer 1

创建一个辅助日历表，其中包含每个日期和外连接(如果 created_at 有时间组件，下面可能需要调整)

SELECT COUNT(*) AS 'count', 
       WEEK(c.date) AS 'week',
       MONTH(c.date) AS 'month', 
       YEAR(c.date) AS 'year', 
       DATE_FORMAT(c.date, '%y') AS 'short_year'
FROM calendar c
LEFT OUTER JOIN jobs j ON j.created_at = c.date
            AND j.state <> 'draft' 
WHERE c.date > '2010-06-21'
            AND c.date < '2010-08-01'
GROUP BY WEEK(c.date)
ORDER BY WEEK(c.date)