如何基于flight_id匹配，查询与指定员工同航班的最新记录

需求说明 #

• 航班执飞人员表结构：包含id、flight_id、staff_number（员工号）、name（姓名）、crsday（日期字段，VARCHAR类型，数值越大代表时间越近，不可修改表结构）
• 已知本人工号为12345，需要查询指定其他员工列表中，每个员工和本人共同执飞的最近一次航班对应记录

方案1：通用兼容写法（支持所有主流SQL数据库） #

不依赖窗口函数，适配MySQL、PostgreSQL、SQL Server等所有常用数据库：

SELECT t2.*
-- 先查询本人执飞的所有航班
FROM (SELECT flight_id, crsday FROM flight_staff WHERE staff_number = '12345') t1
-- 关联查询目标员工执飞的航班
INNER JOIN flight_staff t2 
  ON t1.flight_id = t2.flight_id 
  -- 替换此处为你需要查询的目标员工号列表
  AND t2.staff_number IN ('54976','12293')
-- 过滤出每个目标员工最大crsday对应的最新记录
WHERE (t2.staff_number, t1.crsday) IN (
  SELECT t2_inner.staff_number, MAX(t1_inner.crsday) AS max_crsday
  FROM (SELECT flight_id, crsday FROM flight_staff WHERE staff_number = '12345') t1_inner
  INNER JOIN flight_staff t2_inner 
    ON t1_inner.flight_id = t2_inner.flight_id
    AND t2_inner.staff_number IN ('54976','12293')
  GROUP BY t2_inner.staff_number
)

如果只需要返回共同执飞的航班号，把首行的t2.*换成DISTINCT t2.staff_number, t2.flight_id即可。 #

方案2：窗口函数写法（支持MySQL8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

写法更简洁，执行效率更高：

WITH co_flight AS (
  SELECT 
    t2.*,
    -- 按员工号分组，按crsday倒序排序，排名第一的就是最新同飞记录
    ROW_NUMBER() OVER (PARTITION BY t2.staff_number ORDER BY t1.crsday DESC) AS rn
  FROM (SELECT flight_id, crsday FROM flight_staff WHERE staff_number = '12345') t1
  INNER JOIN flight_staff t2 
    ON t1.flight_id = t2.flight_id
    AND t2.staff_number IN ('54976','12293')
)
SELECT * FROM co_flight WHERE rn = 1

注意事项 #

• 替换代码中IN ('54976','12293')部分的内容为你实际需要查询的目标员工号列表即可
• 因为crsday是字符串类型但数值越大时间越近，直接用MAX函数取最大值即可匹配到最新的日期，不需要额外做类型转换

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