MySQL选择行日期不在日期之间 [英] MySQL select rows where date not between date
问题描述
我有一个预订系统，我需要从数据库中选择任何可用的房间。基本设置是：
table：room
columns：id，maxGuests
table ：roombooking
列：id，startDate，endDate
表：roombooking_room：
列：id，room_id，roombooking_id
我需要选择适合所请求的客人的房间，或者选择两个（或更多）房间以适应客人（由maxGuests定义，显然使用我可以循环遍历我的日期范围并使用这个sql：
SELECT`id`
FROM`room`
WHERE`id` NOT IN
（
SELECT`roombooking_room`.`room_id`
从`roombooking_room`，`roombooking`
WHERE`roombooking`.`confirmed` = 1
AND DATE（％s）BETWEEN`roombooking`.`startDate`和`roombooking`.`endDate`
）
AND`room`.`maxGuests`> =％d
其中％$ 1是循环日期，％2d是要预订的客人数量，但这样我只是返回false，如果有更多的客人比任何房间可以采取，并且必须有一个更快的方法这样做，而不是循环PHP和运行查询？
这类似于我正在考虑的sql的一部分：获取范围之间的日期的日期，但使用Mysql
解决方案，基于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）或`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（$客人> = $ noGuests）break;
$ guests + =（int）$ res ['maxGuests'];
$ rooms [] = $ res ['id'];
}
假设你有兴趣放置 @Guests 从 @StartDate 到 @EndDate
SELECT DISTINCT r.id，
从房间r
LEFT JOIN roombooking_room rbr ON r.id = rbr.room_id
LEFT JOIN roombooking on rbr.roombooking_id = rb.id
WHERE COALESCE（@StartDate NOT BETWEEN rb.startDate AND rb.endDate，TRUE）
AND COALESCE（@EndDate NOT BETWEEN rb.startDate AND rb.endDate，TRUE）
AND @Guests< r.maxGuests
应该给你一个免费的房间的列表，可以容纳一定数量的客人
注意
此查询仅适用于单个房间，如果要查看多个房间您将需要对房间的组合应用相同的标准。为此，您将需要递归查询或一些帮助表。
另外，COALESCE是在那里照顾NULL - 如果一个房间没有预订，它不会有任何记录与日期进行比较，所以它不会返回完全免费的房间。 date1和date2之间的日期将返回NULL，如果date1或date2为空，并且合并将其变为true（替代方案是使用UNION的完全免费房间;可能会更快）。
与多个房间的事情变得非常有趣。
这种情况是你问题的重要部分吗？您使用哪个数据库，即可以访问递归查询？
编辑
以前我多次说过，如果您希望获得最合适的客人数量和客房数量，您寻求解决方案的方法（贪心算法首先查看最大的免费客房）并不是最佳选择。 / p>
所以，如果你替换你的foreach与
$ bestCapacity = 0 ;
$ bestSolution = array（）; （$ i = 1; $ i< = pow（2，sizeof（$ result）） - 1; $ i ++）{
$ b $ solutionGuests = 0;
$ solution = array（）;
$ j = 0;
while（$ solutionIdx> 0）：
if（$ solutionIdx％2 == 1）{
$ solution [] = $ result [$ j] ['id'];
$ solutionGuests + = $ result [$ j] ['maxGuests'];
}
$ solutionIdx = intval（$ solutionIdx / 2）;
$ j ++;
结束;
if（（$ solutionGuests< = $ bestCapacity || $ bestCapacity == 0）&& $ solutionGuests> = $ noGuests）{
$ bestCapacity = $ solutionGuests;
$ bestSolution = $ solution;
}
}
print_r（$ bestSolution）;
print_r（$ bestCapacity）;
将通过所有可能的组合，并找到最少浪费的解决方案空格数。
I have a booking system in which I need to select any available room from the database. The basic setup is:
table: room
columns: id, maxGuests
table: roombooking
columns: id, startDate, endDate
table: roombooking_room:
columns: id, room_id, roombooking_id
I need to select rooms that can fit the requested guests in, or select two (or more) rooms to fit the guests in (as defined by maxGuests, obviously using the lowest/closet maxGuests first)
I could loop through my date range and use this 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
Where %$1 is the looped date and %2d is the number of guests to be booked in. But this will just return false if there are more guests than any room can take, and there must be a quicker way of doing this rather than looping with php and running the query?
This is similar to part of the sql I was thinking of: Getting Dates between a range of dates but with Mysql
Solution, based on ircmaxwell's answer:
$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'];
}
Assuming that you are interested to place @Guests from @StartDate to @EndDate
SELECT DISTINCT r.id,
FROM room r
LEFT JOIN roombooking_room rbr ON r.id = rbr.room_id
LEFT JOIN roombooking ON rbr.roombooking_id = rb.id
WHERE COALESCE(@StartDate NOT BETWEEN rb.startDate AND rb.endDate, TRUE)
AND COALESCE(@EndDate NOT BETWEEN rb.startDate AND rb.endDate, TRUE)
AND @Guests < r.maxGuests
should give you a list of all rooms that are free and can accommodate given number of guests for the given period.
NOTES
This query works only for single rooms, if you want to look at multiple rooms you will need to apply the same criteria to a combination of rooms. For this you would need recursive queries or some helper tables.
Also, COALESCE is there to take care of NULLs - if a room is not booked at all it would not have any records with dates to compare to, so it would not return completely free rooms. Date between date1 and date2 will return NULL if either date1 or date2 is null and coalesce will turn it to true (alternative is to do a UNION of completely free rooms; which might be faster).
With multiple rooms things get really interesting. Is that scenario big part of your problem? And which database are you using i.e. do you have access to recursive queries?
EDIT
As I stated multiple times before, your way of looking for a solution (greedy algorithm that looks at the largest free rooms first) is not the optimal if you want to get the best fit between required number of guests and rooms.
So, if you replace your foreach with
$bestCapacity = 0;
$bestSolution = array();
for ($i = 1; $i <= pow(2,sizeof($result))-1; $i++) {
$solutionIdx = $i;
$solutionGuests = 0;
$solution = array();
$j = 0;
while ($solutionIdx > 0) :
if ($solutionIdx % 2 == 1) {
$solution[] = $result[$j]['id'];
$solutionGuests += $result[$j]['maxGuests'];
}
$solutionIdx = intval($solutionIdx/2);
$j++;
endwhile;
if (($solutionGuests <= $bestCapacity || $bestCapacity == 0) && $solutionGuests >= $noGuests) {
$bestCapacity = $solutionGuests;
$bestSolution = $solution;
}
}
print_r($bestSolution);
print_r($bestCapacity);
Will go through all possible combinations and find the solution that wastes the least number of spaces.
这篇关于MySQL选择行日期不在日期之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
