MySQL:选择未保留的可用车辆

Question

我在 MySQL 数据库的 reservation 表中有以下两条记录。

当我使用此查询获取可用的车辆时，它似乎为我提供了所有车辆，而不仅仅是指定日期之间不在预订表中的车辆。

这是查询:

SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent 
FROM vehicle as v 
WHERE v.id 
NOT IN(
  (SELECT v.id 
  FROM vehicle as v 
  LEFT JOIN reservation as r on r.`vehicle_id` = v.id 
  WHERE r.status_id in(3,4,5) 
  AND (
    (
      ('2014-01-01' >= r.startdate AND '2014-01-03' <= r.enddate )
      OR
      ('2014-01-01' <= r.startdate AND '2014-01-03' >= r.enddate ) 
    ) 
    OR
    (
      ('2014-01-01' >= r.startdate AND '2014-01-01' <= r.enddate)
      OR
      ('2014-01-03' >= r.startdate AND '2014-01-03' <= r.enddate)
    )
  )
  GROUP BY v.id
  )
)

当我使用这个查询时:

SELECT *
FROM reservation as r
WHERE (
  (
    ('2014-01-01' >= r.startdate AND '2014-01-03' <= r.enddate )
    OR
    ('2014-01-01' <= r.startdate AND '2014-01-03' >= r.enddate ) 
  ) 
  OR
  (
    ('2014-01-01' >= r.startdate AND '2014-01-01' <= r.enddate)
    OR
    ('2014-01-03' >= r.startdate AND '2014-01-03' <= r.enddate)
  )
)

为了仅获得预订，我得到了 1 条记录，如您在图片中所见。

可能出了什么问题，我该如何解决？

Answer 1

试试这个 -

SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent
FROM vehicle AS v
LEFT JOIN reservation AS r
  ON v.id = r.vehicle_id
    AND r.startdate <= '2014-01-03'
    AND r.enddate >= '2014-01-01'
WHERE r.id IS NULL;

或使用您的状态条目

SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent
FROM vehicle AS v
LEFT JOIN reservation AS r
  ON v.id = r.vehicle_id
    AND r.startdate <= '2014-01-03'
    AND r.enddate >= '2014-01-01'
    AND r.status NOT IN (3,4,5)
WHERE r.id IS NULL;