根据字段组合选择最新结果

本文介绍了根据字段组合选择最新结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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 | +----+---------------------+-------------+-------------+-------+

感谢您的回复！