MySQL每天返回最后一个值 [英] MySQL return last value per day

问题描述

我有以下数据集:

+----+------------+-------+-------+
| ID |    Date    | Time  | Value |
+----+------------+-------+-------+
|  1 | 2015-01-01 | 14:00 |     1 |
|  2 | 2015-01-01 | 16:00 |     2 |
|  3 | 2015-01-02 | 14:00 |    15 |
|  3 | 2015-01-02 | 17:00 |    12 |
+----+------------+-------+-------+

现在我想返回每天的最后一个值，结果应该是这样的:

Now I want to return the last value for each day and the result should look like that:

+----+------------+-------+-------+
| ID |    Date    | Time  | Value |
+----+------------+-------+-------+
|  2 | 2015-01-01 | 16:00 |     2 |
|  3 | 2015-01-02 | 17:00 |    12 |
+----+------------+-------+-------+

我正在使用以下代码以某种方式提供接近的结果:

I am using the following code that somehow delivers close results:

SELECT DISTINCT     t.id,
                    t.date,
                    t.time,
                    t.balance
FROM db1 t
JOIN (
      SELECT MAX(tt.time) 'maxtime' 
      FROM db1 tt
      GROUP BY tt.date) m ON m.maxtime = t.time

现在返回大多数日期所需的最后一个值.但是，此代码还提供了一些不是最后一个值的随机行.

This now returns the desired last value for a majority of the dates. However, this code also deliveres some random lines that are not the last value.

非常感谢您的帮助，干杯

Many thanks already now for your help, cheers

推荐答案

你已经接近了.您只需要检查日期:

You are close. You just need to check for the date:

SELECT t.id, t.date, t.time, t.balance
FROM db1 t JOIN
     (SELECT tt.date, MAX(tt.time) as maxtime
      FROM db1 tt
      GROUP BY tt.date
     ) m
     ON m.maxtime = t.time AND m.date = t.date;
-------------------------------^

SELECT DISTINCT 不是必需的，除非您认为两条记录可能具有完全相同的时间.

SELECT DISTINCT is not necessary unless you think that two records might have exactly the same time.

这篇关于MySQL每天返回最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！