如何按每个WayBillNo的最大Date获取对应Id

问题描述 #

现有如下物流状态数据表（含示例数据）：

Id  WayBillNo       Date                       Status
------------------------------------------------------------------------------
    1   501263027308    2023-03-10 23:59:59.000    DELIVERED
    2   501263027308    2023-03-10 09:01:12.000    OUT FOR DELIVERY
    3   501263027308    2023-03-09 16:53:25.000    IN-TRANSIT
    4   501263027308    2023-03-09 03:40:37.000    IN-TRANSIT
    5   501263027308    2023-03-08 01:36:24.000    ARRIVED AT DESTINATI
    6   501263027308    2023-03-03 15:59:23.000    IN-TRANSIT
    7   501263027308    2023-03-01 20:50:38.000    IN-TRANSIT
    8   501263027312    2023-03-05 12:11:29.000    ARRIVED AT DESTINATI
    9   501263027312    2023-03-04 23:27:27.000    IN-TRANSIT
    10  501263027312    2023-03-03 19:11:06.000    IN-TRANSIT
    11  501263027312    2023-03-02 14:26:06.000    IN-TRANSIT
    12  501263027312    2023-03-01 20:50:38.000    IN-TRANSIT

需求：提取每个WayBillNo（运单号）对应**最新时间（最大Date）**的记录关联的Id，期望输出结果：

Id
---
1   
8

解决方案 #

方法1：子查询关联（兼容多数SQL数据库） #

先按运单号分组，获取每组的最大时间，再关联原表匹配时间与运单号，得到对应的Id：

SELECT t.Id
FROM your_table_name t
JOIN (
    SELECT WayBillNo, MAX(Date) AS latest_date
    FROM your_table_name
    GROUP BY WayBillNo
) t_latest 
ON t.WayBillNo = t_latest.WayBillNo AND t.Date = t_latest.latest_date;

注意：如果同一运单号存在多条时间完全相同的最新记录，该方法会返回所有对应的Id。

方法2：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数按运单号分组，按时间倒序排序，取每组的第一条记录的Id：

SELECT Id
FROM (
    SELECT 
        Id,
        ROW_NUMBER() OVER (PARTITION BY WayBillNo ORDER BY Date DESC) AS row_rank
    FROM your_table_name
) ranked_records
WHERE row_rank = 1;

注意：若同一运单号有多条时间相同的最新记录，ROW_NUMBER()会随机为其分配序号，仅返回其中一条Id。若需返回所有这类记录，可替换为RANK()或DENSE_RANK()函数。

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