MySQL选择不在日期范围内的行

Question

MySQL选择不在日期范围内的行

5

我需要在预订系统中从数据库中选择任何可用的房间。基本设置如下：

table: room
columns: id, maxGuests

table: roombooking
columns: id, startDate, endDate

table: roombooking_room:
columns: id, room_id, roombooking_id

我需要选择可以容纳所需客人的房间，或者选择两个（或更多）房间以适应客人（根据maxGuests定义，显然首先使用最低/最接近的maxGuests）

我可以循环我的日期范围并使用以下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循环并运行查询？

这类似于我正在考虑的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) 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'];
        }

- Ashley

为什么你们要有一个单独的roombooking_room表？roombooking表：id、room_id、startDate和endDate不足以满足需求吗？ - Axarydax

我认为以现实条件而言，你想要的那个SQL语句会过于复杂，超出了你所尝试达到的目标。循环和使用PHP有什么不对的吗？你可能还会发现，如果用纯SQL实现所需结果，这种解决方案实际上可能比使用PHP循环更慢。但是，我非常有兴趣看到结果，因为我有时也会问类似的问题（PHP vs SQL）。 - Nils Luxton

@Axaryday请查看下面答案的评论。这是必要的，因为一个预订周期可能有多个房间相关联。例如，我和其他9个人一起入住，一个房间最多可以容纳6个人，因此我需要两个房间，但在同一个预订下。 - Ashley

我可能误解了问题。我的理解是您想要检查一系列任意日期（比如八月的每个周末），而不是“在8月1日至14日之间”。如果是这种情况，我很抱歉。 - Nils Luxton

3个回答

3

首先，您使用的内部查询是笛卡尔积，非常昂贵。您需要指定连接条件（例如roombooking_room.booking_id = roombooking.id）。

其次，假设您有一段日期范围，我们能说什么？让我们称您的范围开始时间为rangeStartDate，结束时间为rangeEndDate。

现在，对于任何其他没有任何重叠形式的日期范围，我们能说什么？好吧，endDate不能介于rangeStartDate和rangeEndDate之间。 startDate也是如此。而且，rangeStartDate（和rangeEndDate，但我们不需要检查它）不能介于startDate和endDate之间......

因此，假设%1$s是rangeStartDate，%2$s是rangeEndDate，一个全面的where子句可能是：

WHERE `roomBooking`.`startDate` NOT BETWEEN %1$s AND %2s
    AND `roomBooking`.`endDate` NOT BETWEEN %1$s AND %2$$s
    AND %1s NOT BETWEEN `roomBooking`.`startDate` AND `roomBooking`.`endDate`

但是，有一种更简单的说法。范围被另一个范围排除的唯一方式是起始日期在结束日期之后，或结束日期在起始日期之前。

因此，假设%1$s是rangeStartDate，%2$s是rangeEndDate，另一个全面的where子句可能是：

WHERE `roomBooking`.`startDate` > %2$s
    OR `roomBooking`.`endDate` < %1$s

所以，这就把您整体的查询带到了：

SELECT `id`
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` > %2$s
        OR `roomBooking`.`endDate` < %1$s)
)
AND `room`.`maxGuests`>=%d

还有其他方法可以实现这个目标，所以继续寻找...

- ircmaxell

谢谢，我认为这是前进的方式，当maxGuests小于或等于所请求的客人数量时，这将完美地运行。我想我必须运行这个程序，如果不成功，那么获取maxGuests的房间并从总客人数中减去它，然后再次运行此程序。循环嵌套循环，但我认为这是唯一的方法？ - Ashley

@Ashley，实际上不是这样的，而且你提出的方案也不够全面——你可能会错过一个好的解决方案。考虑到你有3个空闲房间，其中一个有10个空间，另外两个有7个空间，而你想容纳14个人。使用贪心算法，你会占用10和7的房间，错过了两个有7个空间的房间的解决方案。 - Unreason

循环遍历似乎是一个不错的方法。按照 closet maxGuests 组织房间（其中 noGuests >= maxGuests ORDER BY maxGuests limit 1），我认为这样可以吧？ - Ashley

0

SELECT rooms.id
FROM rooms LEFT JOIN bookings
ON booking.room_id = rooms.id
WHERE <booking overlaps date range of interest> AND <wherever else>
GROUP BY rooms.id
HAVING booking.id IS NULL

我可能记错了左连接的工作方式，所以在having语句中你可能需要使用稍微不同的条件，比如count或sum。

最坏的情况下，通过适当的索引，应该只扫描一半的预订记录。

- BCS

如果您不需要聚合，通常不会使用GROUP BY，而在上述情况下，您没有使用任何聚合 - 因此可以取消GROUP BY，对rooms.id使用DISTINCT，并将HAVING移动到WHERE（即使您有聚合/需要group by，也应将条件移至where部分; having用于聚合条件，并且旨在应用于计算聚合之后的结果集）。 - Unreason

@Unreason：虽然这可能适用于IS NULL版本（甚至在二次考虑时是必要的），但对于sum或count版本来说，情况恰恰相反。对于它们来说，过滤器必须应用于聚合结果，因此我使用了HAVING而不是WHERE子句。 - BCS

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Unreason · Accepted Answer

假设您有意将来自@StartDate到@EndDate的@Guests放置。

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

这个查询将给你一个可以容纳指定数量客人并且在指定时间段内空闲的所有房间列表。

注意事项
该查询仅适用于单个房间，如果您想查看多个房间，则需要将相同的条件应用于一组房间。对此，您需要使用递归查询或一些辅助表。另外，COALESCE用于处理NULL值 - 如果一个房间根本没有预订，它就不会有任何记录与日期进行比较，因此它不会返回完全空闲的房间。日期在date1和date2之间将在date1或date2为null时返回NULL，而coalesce将其转换为true（替代方法是联合完全空闲的房间；这可能更快）。

当涉及到多个房间时，情况变得非常有趣。这种情况是您问题的重要部分吗？您使用哪个数据库，即是否可以访问递归查询？

编辑

正如我之前多次声明的那样，如果您想要在所需客人数量和房间之间获得最佳匹配，则贪婪算法（先查找最大的空闲房间）不是最优解决方案。

因此，如果您用以下内容替换您的foreach

$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);

将会遍历所有可能的组合，并找到浪费最少空间的解决方案。