您可以使用子查询进行过滤:
SELECT
kf_ID_Client_Reference_Number,TimeStamp_Creation,Clause,NC_type,NC_Rect_Received,Audit_Num
FROM Audit_Non_Conformance_Records a
where TimeStamp_Creation = (
select max(TimeStamp_Creation)
from Audit_Non_Conformance_Records
)
这将为您提供TimeStamp_Creation
等于表中可用较大值的所有人。
如果您希望所有记录具有最长的一天(排除时间),则可以执行以下操作:
SELECT
kf_ID_Client_Reference_Number,Audit_Num
FROM Audit_Non_Conformance_Records a
where cast(TimeStamp_Creation as date) = (
select cast(max(TimeStamp_Creation) as date)
from Audit_Non_Conformance_Records
)
修改
如果您想要每个refNumber
的最新记录,则可以将子查询关联起来,如下所示:
SELECT
kf_ID_Client_Reference_Number,Audit_Num
FROM Audit_Non_Conformance_Records a
where TimeStamp_Creation = (
select max(TimeStamp_Creation)
from Audit_Non_Conformance_Records a1
where a1.refNumber = a.refNumber
)
为了提高性能,您希望在(refNumber,TimeStamp_Creation)
上建立索引。
,
如果要在SQL Server中获取最新日期,可以将其表示为:
SELECT TOP (1) WITH TIES ancr.kf_ID_Client_Reference_Number,ancr.TimeStamp_Creation,ancr.Clause,ancr.NC_type,ancr.NC_Rect_Received,ancr.Audit_Num
FROM Audit_Non_Conformance_Records ancr
ORDER BY CONVERT(date,ancr.TimeStamp_Creation) DESC;
SQL Server非常擅长处理转换日期,因此如果它在TimeStamp_Creation
上使用索引,我不会感到惊讶。
本文链接:https://www.f2er.com/3100989.html