MySQL最近12个月中按月记录的总数 [英] Mysql sum of records by month for the last 12 months
问题描述
您好,我正在使用此sql查询来获取基于月份的图表表示形式的最近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
但是,如果一个月没有任何记录,它就不会按预期包含在结果集中,并且我正在使用php进行大量计算以完成我想要的.
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.
我的问题是:是否有一种方法可以检索一个简单的结果集,其中包含过去12个月每个月的驱动器总数,并且如果一个月中有0个驱动器,则还必须将其包括在内-显示在结果中设置.
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.
推荐答案
您需要使用包含每月每个行的表进行外部联接.假设您没有这样的表,则可以使用硬编码的UNION查询即时创建该表:
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
任何没有记录的月份,数据列中的行都将带有NULL
.
Any months with no records will have one row with NULL
in the data columns.
这篇关于MySQL最近12个月中按月记录的总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!