根据字段组合选择最新结果 [英] Select most recent results based on composite of fields

问题描述

我正在使用MySQL，但是我认为这是一个基本的SQL问题.
我不知道还有什么要问的，只是举个例子.

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.

说我的表中有这些数据:

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

如何获取单个Foreign_key的每个密钥的最新信息?

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

例如，假设我想要最新的外键4， 我想要的结果是:

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

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

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

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

顺便说一句，我意识到这并不是大多数人选择存储数据的第一种方式，但是我有自己的理由.也就是说，这些值是彼此分开报告的.

推荐答案

我根据精确地给了我我所需要的.即:

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 |
+----+---------------------+-------------+-------------+-------+

感谢您的回复！

这篇关于根据字段组合选择最新结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！