mysql显示12个月,MySQL最近12个月中按月记录的总数

Hello I'm using this sql query to get the last 12 month records based on month for chart representation:

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

however if there are no records for a month it is not included in the result set as expected, and I'm doing a lot of calculations with php to accomplish what I want.

My question is this: Is there a way to retrieve a simple result set with the sum of drives of each month for the last 12 months AND if there are 0 drives for a month it must be also included-shown in the result set.

解决方案

You need to do an outer join with a table that contains a row for each month. Assuming you don't have such a table, you can create it on the fly with a hard-coded UNION query:

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

Any months with no records will have one row with NULL in the data columns.