如何查询全部符合条件的车辆数据？关联location_orders_cars的SQL问题

需求说明 #

现有两张表cars和location_orders_cars，员工预订车辆后归还时会设置location_orders_cars.is_back标记，同一车辆和用户可能有多条预订记录。需要查询所有未被删除（deleted_by IS NULL）的车辆，满足以下任一条件：

• 无对应预订记录
• 最后一条location_orders_cars记录的is_back不为null

原SQL仅能查询到有预订记录的车辆，无法返回无预订的车辆，需进行修改。

原SQL的问题 #

1. 子查询select car_id, is_back FROM location_orders_cars ORDER BY created_at DESC LIMIT 1仅获取了全局最新的一条订单记录，而非每个车辆的最新订单，导致左连接后大部分车辆无法匹配到正确的最新记录。
2. WHERE条件中的loc.is_back IS NOT NULL直接过滤掉了没有预订记录（loc为NULL）的车辆，不符合需求。

修改后的SQL #

SELECT 
    (c.id::text) as value, 
    concat(c.name , ' ' , c.mark) as label
FROM cars c
LEFT JOIN (
    SELECT 
        car_id, 
        is_back,
        ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY created_at DESC) AS rn
    FROM location_orders_cars
) loc ON loc.car_id = c.id AND loc.rn = 1
WHERE c.deleted_by IS NULL 
  AND (loc.is_back IS NOT NULL OR loc.car_id IS NULL)
LIMIT 200;

逻辑说明 #

• 子查询通过ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY created_at DESC)按车辆分组，每组内按创建时间倒序排序，行号rn=1的记录就是该车辆的最新订单。
• 左连接时仅匹配行号为1的最新订单，确保每个车辆最多关联一条最新订单记录。
• WHERE条件调整为：车辆未被删除，同时满足「最新订单的is_back不为null」或「无关联订单记录（loc.car_id IS NULL）」，覆盖了两种符合需求的车辆类型。

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