mysql选择日期不包含小时_MySQL选择日期不在日期之间的行

我有一个预订系统,我需要从数据库中选择任何可用的房间。基本设置是:

table: room

columns: id, maxGuests

table: roombooking

columns: id, startDate, endDate

table: roombooking_room:

columns: id, room_id, roombooking_id

我可以循环遍历我的日期范围并使用以下sql:

SELECT `id`

FROM `room`

WHERE `id` NOT IN

(

SELECT `roombooking_room`.`room_id`

FROM `roombooking_room`, `roombooking`

WHERE `roombooking`.`confirmed` =1

AND DATE(%s) BETWEEN `roombooking`.`startDate` AND `roombooking`.`endDate`

)

AND `room`.`maxGuests`>=%d

其中,%$1是循环日期,%2d是要预订的来宾数。但是,如果有比任何房间都多的客人,这只会返回false,而且必须有一种更快的方法来完成这项工作,而不是使用php循环并运行查询?

解决方案,基于ircmaxwell的答案:

$query = sprintf(

"SELECT `id`, `maxGuests`

FROM `room`

WHERE `id` NOT IN

(

SELECT `roombooking_room`.`room_id`

FROM `roombooking_room`

JOIN `roombooking` ON `roombooking_room`.`roombooking_id` = `roombooking`.`id`

WHERE `roombooking`.`confirmed` =1

AND (`roomBooking`.`startDate` > DATE(%s) OR `roomBooking`.`endDate` < DATE(%s))

)

AND `maxGuests` <= %d ORDER BY `maxGuests` DESC",

$endDate->toString('yyyy-MM-dd'), $startDate->toString('yyyy-MM-dd'), $noGuests);

$result = $db->query($query);

$result = $result->fetchAll();

$rooms = array();

$guests = 0;

foreach($result as $res) {

if($guests >= $noGuests) break;

$guests += (int)$res['maxGuests'];

$rooms[] = $res['id'];

}