SQL 检查日期之间的房间是否可用 [英] SQL To check if the room is available between the dates

问题描述

我有一个关于数据库查询的问题.我有这张桌子:

I have a question in reference to a query to the database. I have this table:

在另一个窗口中，我有一个包含开始和结束日期输入的表单，我想执行 sql 以确认在此日期之间每天 23 号房间是否可用.我用这个 sql 试试:

In another window, i have a form with start and end date inputs and I want to do a sql to confirm if the room 23 is available between this dates, every day. I try with this sql:

SELECT * FROM this_table 
WHERE room_type_id=23 
AND date="2018-03-06 00:00:00" 
AND date="2018-03-07 00:00:00" 
AND date="2018-03-08 00:00:00" 
AND date="2018-03-09 00:00:00"

此查询不起作用，因为正在检查任何行是否包含所有日期.

This query doesn't work because is checking if any row has all the dates.

我的问题是:我如何查看这几天是否有空房?

My question is: How can i check if the room is available on this days?

推荐答案

这将用是或否回答您的特定问题.我不确定它是否有用，因为日期搜索值在硬编码列表中都是特定的.

This will answer your particular question with yes or no. I'm not sure it's useful since the date search values are all specific in a hard-coded list.

select coalesce(max('no'), 'yes') as available
from tbl t           
where room_type_id = 23 and
    dt in ("2018-03-06 00:00:00", "2018-03-07 00:00:00", 
           "2018-03-08 00:00:00", "2018-03-09 00:00:00");

如果你的逻辑意图是找到至少一个开放日期，那么你可以尝试这样的事情:

If the sense of your logic is intended to find at least one open date then you might try something like this:

select case when count(*) = 4 then 1 else 0 end as whatever_this_means
from tbl t
where room_type_id = 23 and
    dt in ("2018-03-06 00:00:00", "2018-03-07 00:00:00", 
           "2018-03-08 00:00:00", "2018-03-09 00:00:00");

问题将是您现在对值 4 进行了硬编码，该值与稍后查询中列表中的日期数相关.有一些方法可以更动态地处理这个问题，但从您的问题中并不完全清楚做到这一点的最佳方法.

The problem is going to be that you've hard-coded the value 4 now which is tied to the number of dates in the list later in the query. There are ways to handle this more dynamically but it's not entirely clear from your question the best way to do that.

这篇关于SQL 检查日期之间的房间是否可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！