mysql - 时间窗口中每月计数(驱动器)的结果不正确

Question

您好，我有以下查询来获取 12 个月前的时间窗口内每月发生的驱动器。然而，结果并不正确，例如，在我的测试数据库中，2 月份仅发生了 3 个驱动器，但在我的结果集中，它显示了 9 个驱动器。

SELECT mlist.m, count(data.month) as drvs
                     FROM users, (
                           SELECT 'Jan' AS m
                           UNION SELECT 'Feb' AS m
                           UNION SELECT 'Mar' AS m
                           UNION SELECT 'Apr' AS m
                           UNION SELECT 'May' AS m
                           UNION SELECT 'Jun' AS m
                           UNION SELECT 'Jul' AS m
                           UNION SELECT 'Aug' AS m
                           UNION SELECT 'Sep' AS m
                           UNION SELECT 'Oct' AS m
                           UNION SELECT 'Nov' AS m
                           UNION SELECT 'Dec' AS m
                          ) AS mlist LEFT JOIN (SELECT DATE_FORMAT(drives.timestamp, '%b') AS month,
                                 drives.timestamp,
                                 CONCAT(drivers.name, ' ', drivers.surname) as driver,
                                 drivers.id as driver_id
                                 FROM drives, drivers WHERE drives.user = '146'
                                 AND drives.timestamp > DATE_SUB(now(), INTERVAL 12 MONTH)) data
                ON mlist.m = data.month GROUP BY mlist.m

结果:

“四月”，“3”“八月”，“3”“12 月”、“6”“二月”、“9”“一月”，“3”“七月”，“3”“君”、“3”“三月”，“3”“5月3日”“十一月”，“3”“10 月”、“6”“九月”，“3”

Answer 1

这有效:

SELECT  mlist.m, count(data.drive_id) AS drvs
                     FROM users, (
                           SELECT 'Jan' AS m
                           UNION SELECT 'Feb' AS m
                           UNION SELECT 'Mar' AS m
                           UNION SELECT 'Apr' AS m
                           UNION SELECT 'May' AS m
                           UNION SELECT 'Jun' AS m
                           UNION SELECT 'Jul' AS m
                           UNION SELECT 'Aug' AS m
                           UNION SELECT 'Sep' AS m
                           UNION SELECT 'Oct' AS m
                           UNION SELECT 'Nov' AS m
                           UNION SELECT 'Dec' AS m
                          ) AS mlist LEFT JOIN (SELECT DATE_FORMAT(drives.timestamp, '%b') AS month,
                                 drives.id AS drive_id, 
                                 drives.timestamp
                                 FROM drives WHERE drives.user = '146'
                                 AND drives.timestamp > DATE_SUB(now(), INTERVAL 12 MONTH)) data
                ON mlist.m = data.month GROUP BY mlist.m