是否可以做一个简单的查询来计算我在一个确定的时间段内有多少记录,比如一年,一个月,或者一天,有一个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
每月进行统计。
谢谢!
当前回答
或者你可以像这样使用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'
其他回答
.... group by to_char(date, 'YYYY')——> 1989
.... group by to_char(date,'MM')——>05
.... 3 .用to_char(date,'DD')——>
.... group by to_char(date,'MON')——>
.... 9 . group by to_char(date,'YY')——>
如果你想获得一个每月统计数据,每个月的行数按最近一个月排序,那么试试这个:
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中简单的使用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 ');
下面的查询在Oracle Database 12c Release 12.1.0.1.0中为我工作
SELECT COUNT(*)
FROM stats
GROUP BY
extract(MONTH FROM TIMESTAMP),
extract(MONTH FROM TIMESTAMP),
extract(YEAR FROM TIMESTAMP);
完整而简单的解决方案,具有类似的性能,但更短,更灵活的替代方案,目前活跃:
SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')