我正在表格中维护车辆的操作历史记录,现在我想获取每个车辆上最新操作的几个字段。
这就是我尝试过的
SELECT HVeh.RefVehicleModel , HVeh.RefVehStatus , HVeh.ChangedDate
FROM HVeh
WHERE ChangedDate >= @X AND ChangedDate <= @Y
order by HVeh.RefVehicleModel, HVeh.ChangedDate DESC
它返回
上面的查询返回我的东西如下
X 1 2019-09-04 08:21:39.223
X 1 2019-09-03 13:47:48.943
X 1 2019-09-03 11:27:43.813
X 1 2019-09-03 10:57:12.080
但是,我现在只想选择顶行,因为它是最新的条目并且特别感兴趣。
注意:如果我使用 SELECT TOP 1,它只会获取整个返回表的第一行,但是正如我所说,我有多个条目(对于每辆车(,我需要为每个条目提供"top"条目
这是你想要的吗?
SELECT TOP 1 WITH TIES HVeh.RefVehicleModel, HVeh.RefVehStatus, HVeh.ChangedDate
FROM HVeh
WHERE ChangedDate >= @X AND ChangedDate <= @Y
ORDER BY ROW_NUMBER() OVER (PARTITION BY HVeh.RefVehicleModel ORDER BY HVeh.ChangedDate DESC);
如果需要每个车型的最新记录,请使用相关的子查询或窗口函数:
SELECT RefVehicleModel, RefVehStatus, ChangedDate
FROM (SELECT HVeh.RefVehicleModel, HVeh.RefVehStatus, HVeh.ChangedDate,
ROW_NUMBER() OVER (PARTITION BY HVeh.RefVehicleModel ORDER BY HVeh.ChangedDate DESC) as seqnum
FROM HVeh
WHERE ChangedDate >= @X AND ChangedDate <= @Y
) h
WHERE seqnum = 1;
如果您只需要一条记录,那么SELECT TOP (1)
是一个很好的解决方案。
SELECT HVeh.RefVehicleModel , HVeh.RefVehStatus , HVeh.ChangedDate
FROM HVeh
WHERE
convert(DATETIME,ChangedDate)
BETWEEN Convert(DATETIME,CASE WHEN isnull(@X,'')='' THEN ChangedDate ELSE isnull(@X,'') END)
AND Convert(DATETIME, CASE WHEN isnull(@Y,'')='' THEN ChangedDate ELSE isnull(@Y,'') END)
order by HVeh.RefVehicleModel, CONVERT(DATETIME,HVeh.ChangedDate,106) DESC
我认为这个查询将非常适合您....
注意:- @X为"开始日期",@Y
"为"日期"