选择上个月的记录，如果在特定时间窗口内没有记录，则显示0

Question

您好，我使用此查询来获取在一年前的时间窗口中特定月份发生的驱动器的列表。

SELECT COUNT( drives.id ) AS drives, DATE_FORMAT( drives.timestamp,  '%d-%m-%Y' ) AS mdate
FROM drives, users
WHERE drives.user = '146'
AND DATE_FORMAT( drives.timestamp,  '%b' ) =  'Feb'
AND drives.timestamp > DATE_SUB(now(), INTERVAL 12 MONTH) 
GROUP BY DATE(drives.timestamp) ORDER BY drives.timestamp ASC

我得到了以下结果：

drives  mdate
1       14-02-2013
2       17-02-2013

然而，我希望每个月的日期的结果，即使没有找到该日期的记录，并显示0旁边的日期，没有驱动器采取place.The棘手的部分是如何获得确切的日期在1年前的时间窗口的具体月份。我可以用php实现这一点，但我更喜欢更干净的解决方案。

Answer 1

不确定users表在哪里(您正在交叉连接它，但实际上并没有在任何地方使用它)，但是像这样的东西应该可以做您需要的事情(未测试)。

SELECT Sub1.aDay, COUNT( drives.id ) AS drives
FROM
(
    SELECT DATE_ADD('2013-02-01', INTERVAL units.i + tens.i * 10 DAY) AS aDay
    FROM
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    HAVING MONTH(aDay) = 2
) Sub1
LEFT OUTER JOIN drives
ON Sub1.aDay = DATE(drives.timestamp)
WHERE drives.user = '146'
GROUP BY DATE(drives.timestamp) 
ORDER BY drives.timestamp ASC