mysql - 从第一个 MySQL 查询的结果匹配的另一个表中添加列

Question

这是我对所选时间段内可用房间的查询:

SELECT rooms.room_id 
  FROM rooms 
 WHERE rooms.room_id NOT IN ( 
   SELECT reservations.room_id 
     FROM reservations 
    WHERE (     reservations.arrivaldate   >=  $arrival_datetime 
            AND reservations.departuredate <=  $departure_datetime
          ) 
       OR (     reservations.arrivaldate   <=  $arrival_datetime 
            AND reservations.departuredate >=  $arrival_datetime 
          ) 
       OR (     reservations.arrivaldate   <=  $departure_datetime 
            AND reservations.departuredate >=  $departure_datetime 
          )
 );

如何从另一个表 (room_prices_table) 为从上述查询返回的每个 room_id 添加选定时间段(从 $arrival_datetime 到 $departure_datetime)的平均房价列。所以我需要查看名称与 room_id 相同的列...

room_prices_table:
date
room0001
room0002
room0003
...

有点像

SELECT AVG(room0003) 
  FROM room_prices_table 
 WHERE datum IS BETWEEN $arrival_datetime 
                    AND $departure_datetime 

??

Answer 1

是这样的:

SELECT 
     r.room_id,
     COALESCE(AVG(rpt.room0003), 0) AS AVERAGE_RATE
FROM rooms r
     LEFT OUTER JOIN room_prices_table rpt
     ON r.room_id = rpt.room_id 
 AND 
     rpt.datum BETWEEN $arrival_datetime AND $departure_datetime
WHERE 
     r.room_id NOT IN (
     SELECT reservations.room_id 
       FROM reservations 
      WHERE (reservations.arrivaldate >=  $arrival_datetime AND 
             reservations.departuredate <=  $departure_datetime) OR 
            (reservations.arrivaldate <=  $arrival_datetime AND
             reservations.departuredate >=  $arrival_datetime) OR
            (reservations.arrivaldate <=  $departure_datetime AND 
             reservations.departuredate >=  $departure_datetime)
     )
GROUP BY
    r.room_id;

使用 COALESCE function确保对于 room_prices_table 中没有信息的房间，我们得到 0 而不是 NULL。