更新时间:2023-01-29 21:51:24
如果 created 是一个 INT 字段,你应该使用 FROM_UNIXTIME 函数将其转换为日期字段,然后 MONTH 提取月份的函数:
If created is an INT field, you should use FROM_UNIXTIME function to convert it to a date field, and then MONTH function to extract the month:
SELECT Month(FROM_UNIXTIME(created)), Count(*)
FROM yourtable
WHERE FROM_UNIXTIME(created) >= CURDATE() - INTERVAL 1 YEAR
GROUP BY Month(FROM_UNIXTIME(created))
这将计算过去 12 个月内创建的所有行.请注意,***也按年份分组:
this will count all the rows that have been created in the last 12 months. Please notice that it's probably better to also group by the YEAR:
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))
如果您需要计算注册号而不是行数,您可以使用类似
If you need to count the registration numbers instead of the rows, you could use something like
COUNT(registration_number)
跳过空值,或
COUNT(DISTINCT registration_number)
只计算不同的.
编辑
如果您还需要显示 count=0 的月份,我会使用这样的查询来返回当前和上一年的所有月份:
If you also need to show months that have count=0, I would use a query like this that returns all of the months for the current and for the previous year:
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,它返回第一个查询的所有行,并且只返回匹配的第二个查询的行:
And then I'd use a LEFT JOIN, that returns all of the rows of the first query, and only the rows of the second query that matches:
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 日之间的行数,如果这不是正确的行为,请告诉我)
(please notice that here I am considering just the last 12 months, so if we are in the middle April 2013 it will count rows in the interval May 2012 - April 13, if this is not the correct behaviour please let me know)