php - 列出给定日期 SQL 的所有可用汽车

Question

大家好，我正在尝试显示给定日期的所有可用汽车列表，我尝试使用 BETWEEN 但没有成功。在汽车表中添加另一个字段(状态)并查询状态是否是个好主意。有好的办法吗？还是使用 return_date 之间的 booking_date 来显示更好？

预订:

    id  customer_id  car_id  booking_date  return_date  time_stamp      
------  -----------  ------  ------------  -----------  ----------------
     1            2       5  2015-11-04    2015-11-12   15.06.10 20:10  
     2            3       2  2015-11-02    2015-11-13   15.06.10 20:10  
     3            1      11  2015-11-05    2015-11-16   15.06.10 20:10  
     4            4       8  2015-11-10    2015-12-16   15.06.10 20:10  
     5            0       0  0000-00-00    0000-00-00   15.06.10 20:10  

车型:

   id  car_type_id  branch_id  registration_number  color    date_of_manifacturing  base_price_per_day  
------  -----------  ---------  -------------------  -------  ---------------------  --------------------
     1            1          1  BG-123-431           Bela     2010-11-15                               30
     2            2          1  BG-A32-212           Metalik  2015-11-04                               30
     3            3          2  PA-332-421           Metalik  2008-05-03                               35
     4            4          1  SM-312-542           Crna     2014-06-01                               35
     5            5          1  BG-4393-54           Crna     2012-05-03                               50
     6            6          1  NS-423-64            Bela     2010-10-08                               50

汽车:

   id  car_type_id  branch_id  registration_number  color    date_of_manifacturing  base_price_per_day  
------  -----------  ---------  -------------------  -------  ---------------------  --------------------
     1            1          1  BG-123-431           Bela     2010-11-15                               30
     2            2          1  BG-A32-212           Metalik  2015-11-04                               30
     3            3          2  PA-332-421           Metalik  2008-05-03                               35
     4            4          1  SM-312-542           Crna     2014-06-01                               35
     5            5          1  BG-4393-54           Crna     2012-05-03                               50
     6            6          1  NS-423-64            Bela     2010-10-08                               50

我所有的预订都是这样列出的:

SELECT BOOKING.BOOKING_NUMBER, BOOKING.BOOKING_DATE,
BOOKING.RETURN_DATE, AGENCY.NAME, CAR.REGISTRATION_NUMBER,
CAR_TYPE.NAME
FROM BOOKING
JOIN AGENCY ON BOOKING.AGENCY_ID = AGENCY.ID
JOIN CAR ON CAR.ID = BOOKING.CAR_ID
JOIN CAR_TYPE ON CAR_TYPE.ID = CAR.CAR_TYPE_ID

但是对于所有可用的车辆我不知道。有什么例子吗？

Answer 1

您可以使用LEFT JOIN:

SELECT 
  CAR.REGISTRATION_NUMBER,
  CAR_TYPE.NAME,
  AGENCY.NAME, 
  CASE 
    WHEN BOOKING.CAR_ID IS NULL THEN 'Not Booked' 
    ELSE 'Booked' 
  END AS Status,
  BOOKING.BOOKING_NUMBER, 
  BOOKING.BOOKING_DATE,
  BOOKING.RETURN_DATE
FROM CAR
INNER JOIN CAR_TYPE ON CAR_TYPE.ID      = CAR.CAR_TYPE_ID
LEFT JOIN BOOKING  ON CAR.ID            = BOOKING.CAR_ID
LEFT JOIN AGENCY   ON BOOKING.AGENCY_ID = AGENCY.ID;

这将为您提供所有可用的汽车，如果任何汽车没有预订，它将带有 NULL 预订和代理值，并且 Status 列将显示是否预订。