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屋！