SQL:如何基于最大日期时间为多个ID选择单个记录? [英] SQL: how to select single record for multiple id's on the basis of max datetime?
本文介绍了SQL:如何基于最大日期时间为多个ID选择单个记录?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下SQL表,
Id WindSpeed DateTime
--------------------------------------
1 1.1 2009-09-14 16:11:38.383
1 1.9 2009-09-15 16:11:38.383
1 2.0 2009-09-16 16:11:38.383
1 1.8 2009-09-17 16:11:38.383
1 1.7 2009-09-19 16:11:38.382
2 1.9 2009-09-19 16:11:38.383
1 1.6 2009-09-19 16:11:38.383
2 1.2 2009-09-20 16:11:38.383
我想编写一个查询,该查询将从上表中返回以下结果集:
I want to write a query which will return me the following result set from the above table:
Id WindSpeed DateTime
--------------------------------------
1 1.6 2009-09-19 16:11:38.383
2 1.2 2009-09-20 16:11:38.383
上面的重用包含最新的(基于该id的最新日期时间)单个条目.这意味着我的日期时间有多个记录ID.
The above reuslt contains the latest (on the basis of latest datetime for that id) single entry. Which means I have multiple record id's with datetime.
我想获取所有ID的最新单项.
I want to get the latest single entry of all id's.
推荐答案
SELECT a.Id, a.WindSpeed, a.DateTime
FROM YourTable AS a
INNER JOIN
(
SELECT ID, Max(DateTime) AS DateTime
FROM YourTable
GROUP BY ID
) AS b
ON a.ID = b.ID
AND a.DateTime = b.DateTime
这篇关于SQL:如何基于最大日期时间为多个ID选择单个记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文