多表关联查询时，如何关联booking表的最新单条数据？

我正在进行多表关联查询，需求是关联booking表时，仅获取对应car_id的最新一条数据。当前使用的SQL语句如下：

SELECT
  car_details.mark,
  cars.name,
  cars.active,
  cars.status,
  station.id as station_id,
  station.location,
  booking.id as booking_id
FROM car_details
INNER JOIN cars
  ON car_details.car_id = cars.id
INNER JOIN car_station
  ON car_station.car_id = cars.id
INNER JOIN station
  ON station.id = car_station.station_id
INNER JOIN booking
  ON booking.car_id = cars.id
WHERE cars.id = $1
LIMIT 1;

期望得到的结果结构如下：

{
  "rent_status": ... (此处需要对应car_id的最新booking_id，因存在多条预订记录),
  "active": ...,
  "name": ...,
  "station": ....
}

请问该如何修改语句以实现需求？

解决方案 #

方法1：使用窗口函数（推荐，适配PostgreSQL、MySQL 8+、SQL Server等主流数据库） #

通过ROW_NUMBER()窗口函数对同一car_id的booking记录按时间排序，筛选出序号为1的最新记录：

SELECT
  cd.mark,
  c.name,
  c.active,
  c.status,
  s.id as station_id,
  s.location,
  b.booking_id as rent_status
FROM car_details cd
INNER JOIN cars c ON cd.car_id = c.id
INNER JOIN car_station cs ON cs.car_id = c.id
INNER JOIN station s ON s.id = cs.station_id
INNER JOIN (
  SELECT
    id as booking_id,
    car_id,
    ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY create_time DESC) as rn
  FROM booking
) b ON b.car_id = c.id AND b.rn = 1
WHERE c.id = $1;

注意：将create_time替换为booking表中实际记录创建/更新时间的字段，确保按最新时间排序。如果没有时间字段，可改用自增的id字段（假设id值越大记录越新）。

方法2：使用子查询直接获取最新booking记录 #

通过子查询筛选出目标car_id对应的最新booking ID，再关联主表：

SELECT
  car_details.mark,
  cars.name,
  cars.active,
  cars.status,
  station.id as station_id,
  station.location,
  booking.id as rent_status
FROM car_details
INNER JOIN cars ON car_details.car_id = cars.id
INNER JOIN car_station ON car_station.car_id = cars.id
INNER JOIN station ON station.id = car_station.station_id
INNER JOIN booking ON booking.car_id = cars.id
WHERE cars.id = $1
  AND booking.id = (
    SELECT MAX(id) FROM booking WHERE car_id = $1
  );

说明：此方法假设booking表的id是自增主键，最新记录的id值最大。如果依赖时间字段判断新旧，可将子查询改为SELECT id FROM booking WHERE car_id = $1 ORDER BY create_time DESC LIMIT 1（适配MySQL、PostgreSQL），或SELECT TOP 1 id FROM booking WHERE car_id = $1 ORDER BY create_time DESC（适配SQL Server）。

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