如果创建的是INT字段,则应使用FROM_UNIXTIME函数将其转换为日期字段,然后使用MONTH函数提取月份:
SELECT Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(created))
这将计算最近12个月中创建的所有行。请注意,最好按年份分组:
SELECT Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Year(FROM_UNIXTIME(created)), Month(FROM_UNIXTIME(created))
如果您需要计算注册号而不是行数,则可以使用类似
COUNT(registration_number)
跳过空值,或
COUNT(DISTINCT registration_number)
仅计算不同的。
如果您还需要显示count = 0的月份,我将使用类似这样的查询,该查询返回当前年份和上一年的所有月份:
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months;
然后,我将使用LEFT JOIN,它返回第一个查询的所有行,并且仅返回匹配的第二个查询的行:
SELECT y, m, Count(yourtable.created)
FROM (
SELECT y, m
FROM
(SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
LEFT JOIN yourtable
ON ym.y = YEAR(FROM_UNIXTIME(yourtable.created))
AND ym.m = MONTH(FROM_UNIXTIME(yourtable.created))
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY y, m
(请注意,这里我只是考虑最近的12个月,因此,如果我们处于2013年4月中旬,它将计算2012年5月至4月13日之间的行数,如果这不是正确的行为,请告诉我)