如何选择一天中每小时的最新记录
我想根据日期时间列'reading_on'选择给定日期中每个小时的最新记录。我执行了下面的查询如何选择一天中每小时的最新记录
hourly_max = InverterReading
.where("DATE(reading_on) = ? AND imei = ?", Date.today, "770000000000126")
.group("HOUR(reading_on)")
.having("max(HOUR(reading_on))")
hourly_max.group_by(&:id).each { |k,v| puts v.last.reading_on }
在上面的查询中,我没有得到所需的结果。选择一天中每小时的最新记录的正确方法是什么?下面是表结构
SELECT
HOUR(a.reading_on) As hr, max(a.id),a.reading_on,
date_format(a.reading_on,'%j-%Y-%k')
FROM
InverterReadings a
LEFT JOIN
InverterReadings b
ON
date_format(a.reading_on,'%j-%Y-%k') = date_format(b.reading_on,'%j-%Y-%k')
AND
a.reading_on < b.reading_on
WHERE
b.reading_on is null
group by a.reading_on;
它给出错误的答案请参阅http://sqlfiddle.com/#!2/49a69/7 –
SELECT
HOUR(a.reading_on) As hr, max(a.id),a.reading_on
FROM
InverterReadings a
LEFT JOIN
InverterReadings b
ON
YEAR(a.reading_on)=YEAR(b.reading_on)
AND MONTH(a.reading_on)=MONTH(b.reading_on)
AND day(a.reading_on)=day(b.reading_on)
AND hour(a.reading_on)=hour(b.reading_on)
AND
a.reading_on < b.reading_on
WHERE
b.reading_on is null
group by a.reading_on;
它不会返回基于不同年份同一日期的结果。 –
我已编辑答案。 –
请勿复制答案:P –
可以提供MySQL表结构,如果可能的话? –
@VishalZanzrukia添加了表结构 – loganathan
是否有任何updated_record_time(Date)字段,我们可以通过该字段获取小时数据? –