如何编写 SQL 查询以检查房间的可用性

huangapple go评论55阅读模式
英文:

How to write sql query to check room availability

问题

用户表包含应检查房间表中的可用性的日期范围,房间表包含已预订房间的日期范围。

英文:

The user table contains the dates between which the room in the room table should be checked for availability, the room table contains dates between which the room is booked.

如何编写 SQL 查询以检查房间的可用性image here

code

SELECT room
from room r, user u
where u.indate not between r.checkin and r.checkout and
     u.outdate not between r.checkin and r.checkout union
select room from room r, user u
where u.indate=r.checkout

答案1

得分: 1

以下是翻译好的部分:

如果room表列出了给定房间(由id列定义)占用的日期范围,而您试图确定user表中每个用户(由id列定义)可以使用哪些房间,那么从这两个表中原则上无法确定;有一部分信息缺失。正如已经指出的那样,所谓的room表实际上是一个reservation表,并不一定要列举出酒店中的所有房间。也就是说,酒店中可能有一间房间,目前有或从未有过预订,并且在rooms表中没有任何行。可以假定这些房间对所有用户都可用。但是,如果我们假设目前酒店中的每个房间都至少由rooms表中的一行表示,我们可以继续。但最终我们仍然要么是一个问题提出不清晰,要么是一个数据库设计不良。

逻辑是:如果不存在这样的room.id,即

`user.outdate` >= `room.checkin` 并且 `user.indate` <= `room.checkout`

room.iduser.id可用。

SQL查询:

select distinct u.id, ifnull(r.id, '') as room_id from
user u left join room r on r.id not in (
   select room.id from room where u.outdate >= room.checkin and u.indate <= room.checkout
);

查看 Db Fiddle

或者,如果您想将所有房间合并到一个列中:

select id, group_concat(room_id) from (
    select distinct u.id, ifnull(r.id, '') as room_id from
	user u left join room r on r.id not in (
   	    select room.id from room where u.outdate >= room.checkin and u.indate <= room.checkout
    )
) sq
group by id

DB Fiddle

英文:

If the room table lists date ranges when a given room, defined by the id column is occupied and you are trying to determine for each user, defined by the id column in the user table which rooms would be available, then you can't in principle from these two tables; there is a piece missing. As been pointed out, the so-called room table is really a reservation table and does not by necessity enumerate all the rooms in the hotel. That is, there could be a room in the hotel that currently has or has never had a reservation and has no rows in the rooms table. Presumably those rooms would be available to all users. But if we proceed on the assumption that currently every room in the hotel is represented by at least one row in the rooms table, we may proceed. But in the end what we still have is either a question poorly posed or a poor database design.

The logic is: A room.id is available to a user.id if there does not exist a room.id such that:

`user.outdate` &gt;= `room.checkin` and `user.indate` &lt;= `room.checkout`

The SQL:

select distinct u.id, ifnull(r.id, &#39;&#39;) as room_id from
user u left join room r on r.id not in (
   select room.id from room where u.outdate &gt;= room.checkin and u.indate &lt;= room.checkout
);

See Db Fiddle

Or if you want to combine all the rooms into one column:

select id, group_concat(room_id) from (
    select distinct u.id, ifnull(r.id, &#39;&#39;) as room_id from
	user u left join room r on r.id not in (
   	    select room.id from room where u.outdate &gt;= room.checkin and u.indate &lt;= room.checkout
    )
) sq
group by id

DB Fiddle

答案2

得分: 0

如果您想要查找可用的房间,您需要一个名为"rooms"的表,每个房间有一行记录。否则,您将永远无法找到没有预订的房间。您所称之为的"room_table"实际上是预订信息。

然后,您可以使用"not exists":

select u.*, r.room_id
from users u cross join
     rooms r
where not exists (select 1
                  from reservations re
                  where re.room_id = r.room_id and
                        re.checkin < u.outdate and
                        re.checkout > u.indate
                 );

重叠逻辑很简单。如果没有与时间段重叠的预订记录,房间就是可用的。

英文:

If you want rooms available you need a "room"s table with one row per rooms. Otherwise, you will never find rooms that have no reservations. What you are calling room_table is really reservations.

Then you can use not exists:

select u.*, r.room_id
from users u cross join
     rooms r
where not exists (select 1
                  from reservations re
                  where re.room_id = r.room_id and
                        re.checkin &lt; u.outdate and
                        re.checkout &gt; u.indate
                 );

The overlap logic is simple. A room is available if there are no reservations that overlaps with the period of time.

huangapple
  • 本文由 发表于 2020年1月4日 01:35:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/59582918.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定