PostgreSQL中如何展示重复车辆的最早预订记录？

要只保留每辆车的最早预订记录，可以用**窗口函数ROW_NUMBER()**来实现，核心思路是按车辆分组，给每组内的预订记录按预订日期排序，取排序后第一条（最早）的记录。修改后的查询语句如下：

WITH filtered_bookings AS (
    SELECT 
        booking.id,
        cars.name,
        cars.cover AS image,
        car_details.mark,
        client.surname,
        client.lastname,
        booking_car_status.booking_id AS booking_car_status_id,
        -- 按车辆分组，按预订日期升序、预订ID升序排序，给每条记录标序号
        ROW_NUMBER() OVER (PARTITION BY cars.id ORDER BY booking.from_date ASC, booking.id ASC) AS rn
    FROM booking
    INNER JOIN cars ON booking.car_id = cars.id
    INNER JOIN car_details ON car_details.car_id = cars.id
    INNER JOIN client ON client.id = booking.client_id
    LEFT JOIN booking_car_status ON booking_car_status.booking_id = booking.id
    LEFT JOIN booking_signature ON booking_signature.booking_id = booking.id
    WHERE booking_car_status.booking_id IS NULL 
      AND booking.from_date::date <= NOW()
)
SELECT id, name, image, mark, surname, lastname, booking_car_status_id
FROM filtered_bookings
WHERE rn = 1 -- 只保留每组内第一条（最早）的记录
ORDER BY id ASC;

关键说明： #

• PARTITION BY cars.id：按车辆ID分组，确保同一辆车的所有预订记录被分到同一组
• ORDER BY booking.from_date ASC, booking.id ASC：先按预订日期升序（最早的排前面），如果同一日期有多条预订，再按预订ID升序取最早创建的那条
• rn = 1：筛选出每组内排序后的第一条记录，也就是该车辆的最早预订记录

另外，原查询里的LEFT JOIN booking_signature在最终SELECT里没有用到字段，如果不需要关联这张表，可以直接删掉这一行来优化查询性能。

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