时间戳

首页 > 解决方案 > SQL如何返回具有最近日期和最高int值的记录

sql - SQL如何返回具有最近日期和最高int值的记录

问题描述

我必须返回 SQL 数据库中的最新行，按CallID. 在这种情况下，“最近”是指该日期最近的 DepartureDate 和最高的 Departuretime (int)。请参阅以下示例供您参考：

set dateformat dmy
create table #Temp
(
Callid nvarchar(6), 
Linenum nVarchar(6), 
Engineerid nvarchar (4), 
Departuredate DateTime, 
Departuretime int
)
insert into #Temp 
Values (
'100000','1','AToo','05/09/2017','57896'),
('100000','1.5','DBok','05/09/2017','57898'),
('100000','1.75','DBok','05/09/2017','57895'),
('100000','2','GKar','04/09/2017','59805'),
('100000','3','ALee','05/09/2017','54895'),
('100001','1','GKar','08/09/2017','54000'),
('100001','2','GKar','08/09/2017','58895'),
('100001','2.25','ALee','08/09/2017','56875'),
('100001','2.5','DBok','07/09/2017','59000')

select * from #Temp
drop table #Temp

我想为上述返回的是以下两条记录：

CallID  Linenum  Engineerid  Departuredate  Departuretime
100000  1.5      DBok        05/09/2017     57898
100001  2        GKar        08/09/2017     58895

标签： sqlsql-servertsqlselecttop-n

解决方案

您可以使用row_number窗口函数对记录进行排序callid，然后只取第一个：

SELECT CallID, Linenum, Engineerid, Departuredate, Departuretime
FROM   (SELECT CallID, Linenum, Engineerid, Departuredate, Departuretime,
               ROW_NUMBER() OVER (PARTITION BY CallID
                                  ORDER BY Departuredate DESC, Departuretime DESC) AS rn
        FROM   #Temp) t
WHERE  rn = 1

推荐阅读