SQL Server查询最新记录：如何获取指定机场的最新航班数据？

我尝试过类似的查询方法，但仍未得到预期结果。以下是三条示例记录：

create table #temps
(
    airport varchar(10),
    country varchar(10),
    plane varchar(10),
    id int,
    flight_date datetime
)

insert into #temps 
values ('IAD', 'USA', 'a777', '195', '  7/26/2022 11:39:00 AM')

insert into #temps 
values ('IAD', 'USA', 'a777', '195', '  8/12/2022 9:51:00 AM')

insert into #temps 
values ('BOS', 'USA', 'a777', '195', '  8/12/2022 9:51:00 AM')

我希望检索到来自BOS机场的最新记录（暂不考虑不同机场出现相同航班日期的情况）。

我尝试了两种方法：

1. 使用ROW_NUMBER函数，语句如下，期望返回排名值最大的记录：

SELECT DISTINCT
     a.airport, a.country, a.flight_date, a.plane, id,
     ROW_NUMBER() OVER (PARTITION BY id ORDER BY flight_date ASC) AS Ct
 FROM
     #temps a 

2. 使用MAX函数关联查询，语句如下：

SELECT A.airport, A.id, A.flight_date, A.country
 FROM #temps A
 INNER JOIN (SELECT id, MAX(flight_date) as MAX_FLIGHT_DATE 
             FROM #temps 
             GROUP BY id) B ON (A.flight_date = B.MAX_FLIGHT_DATE)

请问是否有更合适的方法可以返回BOS机场的这条记录？

感谢！
Joe

方法一：优化ROW_NUMBER函数 #

你之前的写法没有限定BOS机场，也没筛选排名结果。可以调整为：

WITH ranked_flights AS (
    SELECT 
        airport, country, plane, id, flight_date,
        ROW_NUMBER() OVER (PARTITION BY id, airport ORDER BY flight_date DESC) AS rn
    FROM #temps
    WHERE airport = 'BOS'
)
SELECT airport, country, plane, id, flight_date
FROM ranked_flights
WHERE rn = 1;

通过CTE先对BOS机场的记录按航班日期倒序排名，取排名为1的就是该机场的最新记录，适配你暂不考虑同日期多记录的需求。

方法二：直接筛选BOS+MAX日期 #

这种写法更直接，先锁定BOS机场的最大航班日期，再匹配对应记录：

SELECT t.airport, t.country, t.plane, t.id, t.flight_date
FROM #temps t
INNER JOIN (
    SELECT MAX(flight_date) AS max_date
    FROM #temps
    WHERE airport = 'BOS'
) m ON t.flight_date = m.max_date AND t.airport = 'BOS';

方法三：TOP 1 排序法 #

如果确定BOS机场的最新记录仅一条（或只需取第一条），这是最简洁的写法：

SELECT TOP 1 airport, country, plane, id, flight_date
FROM #temps
WHERE airport = 'BOS'
ORDER BY flight_date DESC;

内容的提问来源于stack exchange，提问作者Hung Captain