使用MySQL检索按小时分组的行 [英] Retrieve rows grouped by hour with MySQL

问题描述

我有一个包含访问日志的表。我想知道在24小时内每小时有多少次访问resource_id'123'。

I have a table containing access logs. I want to know how many accesses to resource_id '123' occured in each hour in a 24 hour day.

我首先想到的是检索这些信息只是循环遍历每个小时并在每个循环中查询表格，例如... 和时间'$ hour：％'，因为时间字段保存格式为 15:47:55 的数据。

My first thought for retrieving this info is just looping through each hour and querying the table in each loop with something like... and time like '$hour:%', given that the time field holds data in the format 15:47:55.

有没有一种方法可以 group by by 小时数并检索每个小时的单个查询中每个小时内的行数

Is there a way I can group by the hours and retrieve each hour and the number of rows within each hour in a single query?

数据库是MySQL，language是PHP。

Database is MySQL, language is PHP.

推荐答案

SELECT HOUR(MyDatetimeColumn) AS h, COUNT(*)
FROM MyTable
GROUP BY h;

这篇关于使用MySQL检索按小时分组的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！