Mysql - 根据数学运算和 SUM() 函数对结果进行分组

Question

我有以下两个表...

表：room_type

 type_id    type_name   no_of_rooms     max_guests  rate    
  1          Type 1         15               2      1254    
  2          Type 2         10               1      3025

表：预订

reservation_id  start_date  end_date    room_type   booked_rooms    
       1        2010-04-12  2010-04-15      1            8 
       2        2010-04-12  2010-04-15      1            2 

现在... 我有这个查询

SELECT type_id, type_name
FROM room_type
WHERE id NOT IN (SELECT room_type
             FROM reservation
             WHERE start_date >= '$start_date'
             AND   end_date <= '$end_date')

该查询的作用是选择在开始日期和结束日期之间未预订的房间。

另外，正如您从预订表中看到的，我们还有“两个日期之间预订的房间数量”因素...

我需要将这个“两个日期之间预订的房间数量”因素也添加到查询...

查询应返回两个日期之间至少有一个房间空闲的房间类型。

我弄清楚了逻辑，但无法将其表示为查询......！你将如何做到这一点...？

感谢您的建议..！

Answer 1

你的结果可能会在这个

 SELECT a.type_id, a.type_name, a.no_of_rooms, (

SELECT SUM( booked_rooms )
FROM reservation
WHERE room_type = a.type_id
AND start_date >= '2010-04-12'
AND end_date <= '2010-04-15'
) AS booked_rooms, (
a.no_of_rooms - (
SELECT SUM( booked_rooms )
FROM reservation
WHERE room_type = a.type_id
AND start_date >= '2010-04-12'
AND end_date <= '2010-04-15' )
) AS freerooms
FROM room_type AS a
LEFT JOIN reservation AS b ON a.type_id = b.room_type
GROUP BY a.type_id
ORDER BY a.type_id