我正在使用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?
顺便说一句，我意识到这并不是大多数人选择存储数据的第一种方式，但是我有自己的理由.也就是说，这些值是彼此分开报告的.
推荐答案我根据此页面.查询
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 | +----+---------------------+-------------+-------------+-------+感谢您的回复！