Сумма записей Mysql по месяцам за последние 12 месяцев

Здравствуйте, я использую этот 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 дисков, он также должен быть включен в набор результатов.


mysql sql gaps-and-islands
person syd619    schedule 03.11.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Вам нужно выполнить внешнее соединение с таблицей, содержащей строку для каждого месяца. Предполагая, что у вас нет такой таблицы, вы можете создать ее на лету с помощью жестко закодированного запроса 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 в столбцах данных.

person Barmar    schedule 03.11.2013