根据字段的组合选择最近的结果

I'm using MySQL, but I think this is a basic SQL question.
I don't know how else to ask but to give an example.

我使用的是MySQL，但我认为这是一个基本的SQL问题。除了举个例子，我不知道还能问什么。

Say I have this data in my table:

假设我的表中有这样的数据:

id    date_time             foreign_key   key             value
1     2010-01-01 00:00:00   1             'temperature'   84
2     2010-01-01 00:00:01   1             'humidity'      34
3     2010-01-01 00:00:02   2             'temperature'   45
4     2010-01-01 00:00:03   2             'humidity'      23
5     2010-01-01 00:00:04   2             'dew_point'     78
6     2010-01-01 00:00:05   3             'temperature'   57
7     2010-01-01 00:00:06   3             'humidity'      41
8     2010-01-01 00:00:07   4             'temperature'   19
9     2010-01-01 00:00:08   4             'humidity'      35
10    2010-01-01 00:00:09   4             'dew_point'     24
11    2010-01-01 00:00:10   1             'temperature'   84
12    2010-01-01 00:00:11   1             'dew_point'     34
13    2010-01-01 00:00:12   2             'temperature'   45
14    2010-01-01 00:00:13   2             'humidity'      23
15    2010-01-01 00:00:14   3             'dew_point'     57
16    2010-01-01 00:00:15   3             'humidity'      41
17    2010-01-01 00:00:16   4             'temperature'   19
18    2010-01-01 00:00:17   4             'dew_point'     24

How do I get most recent of each key for a single foreign_key?

如何获取单个foreign_key的每个键的最新信息?

For Example, say I want the most recent for the foreign_key of 4, the result I want would be:

例如，假设我想要最近的4的foreign_key，我想要的结果是:

id    date_time             foreign_key   key             value
9     2010-01-01 00:00:08   4             'humidity'      35
17    2010-01-01 00:00:16   4             'temperature'   19
18    2010-01-01 00:00:17   4             'dew_point'     24

What is the SQL I would use to achieve this result?

我将使用什么SQL来实现这个结果?

As an aside, I realize that this is not the first way most would choose to store data like this, but I have my reasons. Namely that these values are reported separately from each other.

顺便说一句，我意识到这并不是大多数人首选的存储数据的方式，但我有我的理由。即这些值是分别报告的。

4 个解决方案

#1

select m.id, m.date_time, m.foreign_key, m.key, m.value 
from (
    select foreign_key, key, max(date_time) as MaxDate
    from MyTable
    group by foreign_key, key
) mm
inner join MyTable m on mm.foreign_key = m.foreign_key
    and mm.MaxDate = m.date_time
    and mm.key = m.key

#2

select m.id, m.date_time, m.foreign_key, m.key, m.value 
from (
    select foreign_key, key, max(date_time) as MaxDate
    from MyTable
    group by foreign_key, key
) mm
inner join MyTable m on mm.foreign_key=m.foreign_key
    and mm.MaxDate = m.date_time and mm.key = m.key

As I commented above, credit should go to RedFilter for getting there first.

正如我在上面评论的那样，信贷应该首先进入RedFilter。

#3

SELECT * FROM table WHERE foreign_key='4' ORDER BY date_time ASC LIMIT 3;

从表中选择*，其中foreign_key='4' ORDER BY date_time ASC LIMIT 3;

update, then the correct would be:

更新，则正确的是:

SELECT * FROM `table` WHERE `foreign_key`='4' GROUP BY `key` HAVING `date_time`=MAX(`date_time`) ORDER BY `id`;

#4

I figured it out based on an answer on this page. The query

我是根据这一页上的答案算出来的。查询

SELECT m.id, m.date_time, m.foreign_key, m.key, m.value
FROM MyTable m
LEFT OUTER JOIN MyTable mm
  ON (m.foreign_key = mm.foreign_key
      AND m.key = mm.key
      AND m.date_time < mm.date_time)
WHERE mm.key IS NULL
  AND m.foreign_key=4;

gives me exactly what I needed. Namely:

给我我所需要的。即:

+----+---------------------+-------------+-------------+-------+
| id | date_time           | foreign_key | key         | value |
+----+---------------------+-------------+-------------+-------+
|  9 | 2010-01-01 00:00:08 |           4 | humidity    |    35 |
| 17 | 2010-01-01 00:00:16 |           4 | temperature |    19 |
| 18 | 2010-01-01 00:00:17 |           4 | dew_point   |    24 |
+----+---------------------+-------------+-------------+-------+

Thank you for you responses!

感谢您的回复!