是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录,比如一年,一个月,或者一天,有一个TIMESTAMP字段,比如:
SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR
甚至:
SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH
每月进行统计。
谢谢!
是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录,比如一年,一个月,或者一天,有一个TIMESTAMP字段,比如:
SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR
甚至:
SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH
每月进行统计。
谢谢!
当前回答
我试着使用上面的“WHERE”语句,我认为它是正确的,因为没有人纠正它,但我错了;经过一些搜索,我发现这是WHERE语句的正确公式,所以代码变成这样:
SELECT COUNT(id)
FROM stats
WHERE YEAR(record_date) = 2009
GROUP BY MONTH(record_date)
其他回答
GROUP BY DATE_FORMAT(record_date, '%Y%m')
Note (primarily, to potential downvoters). Presently, this may not be as efficient as other suggestions. Still, I leave it as an alternative, and a one, too, that can serve in seeing how faster other solutions are. (For you can't really tell fast from slow until you see the difference.) Also, as time goes on, changes could be made to MySQL's engine with regard to optimisation so as to make this solution, at some (perhaps, not so distant) point in future, to become quite comparable in efficiency with most others.
GROUP BY YEAR(record_date), MONTH(record_date)
查看MySQL中的日期和时间函数。
你可以在GROUP BY中简单的使用Mysql DATE_FORMAT()函数。在某些情况下,您可能希望添加一个额外的列以增加清晰度,例如记录跨越数年,而同一个月出现在不同的年份。这里有很多选项,你可以自定义。开始前请先读一下。希望对你有帮助。下面是示例查询,以帮助您理解
SELECT
COUNT(id),
DATE_FORMAT(record_date, '%Y-%m-%d') AS DAY,
DATE_FORMAT(record_date, '%Y-%m') AS MONTH,
DATE_FORMAT(record_date, '%Y') AS YEAR
FROM
stats
WHERE
YEAR = 2009
GROUP BY
DATE_FORMAT(record_date, '%Y-%m-%d ');
如果你想获得一个每月统计数据,每个月的行数按最近一个月排序,那么试试这个:
SELECT count(id),
YEAR(record_date),
MONTH(record_date)
FROM `table`
GROUP BY YEAR(record_date),
MONTH(record_date)
ORDER BY YEAR(record_date) DESC,
MONTH(record_date) DESC
或者你可以像这样使用group by子句,
//to get data by month and year do this ->
SELECT FORMAT(TIMESTAMP_COLUMN, 'MMMM yy') AS Month, COUNT(ID) FROM TABLE_NAME GROUP BY FORMAT(TIMESTAMP_COLUMN, 'MMMM yy')
如果要按日期获取记录,则在组中按格式更改为 'dd-mm-yy'或'dd-MMMM-yyy'