Mysql的总和除以一个月的过去12个月

Question

你好，我用这个SQL查询来获取基于每月的图表表示在过去12次月的记录：

SELECT DATE_FORMAT(drives.timestamp, "%b") AS Month, 
         DATE_FORMAT(drives.timestamp, "%d-%m-%Y %H:%i:%s") AS Exact_date, 
         drives.departure, 
         drives.destination, 
         drives.route, 
         CONCAT(drivers.name, " ", drivers.surname) as driver, 
         drivers.id as driver_id 
         FROM drives, drivers WHERE drives.driver = drivers.id 
         AND drives.timestamp > DATE_SUB(now(), INTERVAL 12 MONTH) ORDER BY drives.timestamp Asc 

然而

如果没有记录一个月没有按预期包含在结果集中，而且我正在用php做很多计算来实现我想要的结果。

我的问题是这样的：有没有办法在过去12个月中检索每月驱动器总和的简单结果集，并且如果一个月内有0个驱动器，则它必须也包括在内 - 显示在结果集。

Answer 1

您需要使用包含每个月的行的表进行外连接。假设你没有这样的表，你可以在飞行中使用硬编码的UNION查询创建：

SELECT * FROM 
    (SELECT DATE_FORMAT(now(), "%b") as Month 
    UNION 
    SELECT DATE_FORMAT(now() - INTERVAL 1 MONTH), "%b") 
    UNION 
    SELECT DATE_FORMAT(now() - INTERVAL 2 MONTH), "%b") 
    UNION 
    ... 
    SELECT DATE_FORMAT(now() - INTERVAL 11 MONTH), "%b")) AS Months 
LEFT JOIN (SELECT DATE_FORMAT(drives.timestamp, "%b") AS Month, 
       drives.timestamp, 
       DATE_FORMAT(drives.timestamp, "%d-%m-%Y %H:%i:%s") AS Exact_date, 
       drives.departure, 
       drives.destination, 
       drives.route, 
       CONCAT(drivers.name, " ", drivers.surname) as driver, 
       drivers.id as driver_id 
       FROM drives, drivers WHERE drives.driver = drivers.id 
       AND drives.timestamp > DATE_SUB(now(), INTERVAL 12 MONTH)) data 
ON Months.Month = data.Month 
ORDER BY data.timestamp 

没有记录任何个月内将有一个排，在数据列NULL。