MySQL:如何每小时分组数据并获取最新小时 [英] MySQL: How to group data per hour and get the latest hour
问题描述
我正在尝试执行一个查询,该查询每个hour
都提取数据,但我不想按小时分组,而是想缩小范围,只得到latest
小时-表示该小时内的最新数据.下面显示的图片是我想要得到的带有红色框的行.如果您会注意到,第一个红色行是10:59:51
,这意味着它是10:00:00
和10:59:59
内的唯一行.对于12:00
上的其余行,我想获取12:37:14
,因为它是该小时范围内的最新或最新行情.
I'm trying to do a query that fetches data per hour
but instead of the normal group by hour I want to narrow it down and only get the latest
hour - meaning the newest data within that hour. With the picture shown below what I wanted to get is the rows with red boxes. If you will notice, first red row is 10:59:51
which means it's the only row that's within 10:00:00
and 10:59:59
. For the rest of the rows that is on 12:00
and above I wanted to get 12:37:14
because it's the latest or newest for that hour range.
我有一个简单的查询,该查询使用hour
将数据分组,例如:
I have a simple query that groups the data by hour
using HOUR()
like:
SELECT userid, username, date_created
FROM user_accounts
WHERE date_created >= '2009-10-27 00:00:00' AND date_created < '2009-10-27 23:59:59'
GROUP BY HOUR(date_created)
但是,查询只是按小时10
和12
进行分组,从而返回id 24
和25
-但我需要的是 id 24
和28
.有什么想法吗?
The query, however, is just grouping it by hour 10
and 12
which returns id 24
and 25
- but what I needed is id 24
and 28
. Any ideas?
推荐答案
尝试
SELECT f.*
FROM (
SELECT MAX(UNIX_TIMESTAMP(date_created)) AS mts
FROM user_accounts
GROUP BY HOUR(date_created)
) s
JOIN user_accounts f
ON UNIX_TIMESTAMP(date_created) = s.mts
WHERE DATE(date_created) = '2009-10-27'
这篇关于MySQL:如何每小时分组数据并获取最新小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!