我有以下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
我想编写一个查询,它将从上表返回以下结果集：
Id WindSpeed DateTime -------------------------------------- 1 1.6 2009-09-19 16:11:38.383 2 1.2 2009-09-20 16:11:38.383
以上reuslt包含最新的(基于该id的最新日期时间)单个条目.这意味着我有多个记录ID与日期时间.
我想得到所有id的最新单一条目.
解决方法
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
