blocks|key|351120|text|您试图使用的查询在逻辑上是错误的。内连接在这里没有用。请查看下面的查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|351121|SELECT+Room.*
FROM+Room
WHERE+NOT+EXISTS+
(
+++SELECT+1+FROM+Busy+WHERE+Room.RoomdId=+Busy.RoomdId+AND
+++++(@dateArr+BETWEEN+'Busy.ArrivalDate'+AND+'Busy.DepartureDate')+AND+
+++++(@dateDep+BETWEEN+'Busy.ArrivalDate'+AND+'Busy.DepartureDate')
)|code-block|syntax|javascript|351122|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

the query you are trying to use is logically wrong. Inner join has no use here. Please check the query below

<pre><code>SELECT Room.*
FROM Room
WHERE NOT EXISTS 
(
 SELECT 1 FROM Busy WHERE Room.RoomdId= Busy.RoomdId AND
 (@dateArr BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate') AND 
 (@dateDep BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate')
)
</code></pre>

blocks|key|1658515|text|首先，你对引号有一个问题。但我认为这可能只是把问题中的价值观。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1658516|第二，您不需要busy中的列。没有对手，所以他们不感兴趣。|offset|length|style|CODE|1658517|那么重叠逻辑是不正确的。类似于：|1658518|SELECT+r.*
FROM+Room+r+LEFT+JOIN
+++++Busy+b
+++++ON+r.RoomdId+=+b.RoomId+AND
++++++++@dateArr+<=+b.DepartureDate+AND
++++++++@dateDep+>=+b.ArrivalDate
WHERE+b.RoomId+IS+NULL;|code-block|syntax|javascript|1658519|你的问题不清楚某人是否可以在出发日期到达(反之亦然)。如果是这样的话，<=和>=可能需要失去相等的比较。|1658520|编辑：|1658521|在MS+Access中，您可以使用NOT+EXISTS|1658522|SELECT+r.*
FROM+Room+r+LEFT+JOIN
WHERE+NOT+EXISTS+(SELECT+1
++++++++++++++++++FROM+Busy+b
++++++++++++++++++WHERE+r.RoomdId+=+b.RoomId+AND
++++++++++++++++++++++++@dateArr+<=+b.DepartureDate+AND
++++++++++++++++++++++++@dateDep+>=+b.ArrivalDate
+++++++++++++++++);|1658523|entityMap^0|0|7|4|0|0|0|Z|2|12|2|0|0|H|A|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|13|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|14|8|@$D|15|E|16|F|G]|$D|17|E|18|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@$D|1B|E|1C|F|G]]|9|@]|A|$]]|$1|U|3|V|5|L|7|1D|8|@]|9|@]|A|$M|N]]|$1|W|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|X|$]]

First, you have a problem with the quotes. But I think that is probably just putting the values in the question.

Second, you don't want the columns from <code>busy</code>. There is no match so they are not of interest.

Then the overlap logic is not correct. Something like:

<pre><code>SELECT r.*
FROM Room r LEFT JOIN
 Busy b
 ON r.RoomdId = b.RoomId AND
 @dateArr &lt;= b.DepartureDate AND
 @dateDep &gt;= b.ArrivalDate
WHERE b.RoomId IS NULL;
</code></pre>

Your question is unclear whether someone can arrive on a departure date (and vice versa). If so, the <code>&lt;=</code> and <code>&gt;=</code> might need to lose the equality comparisons.

EDIT:

In MS Access, you can use <code>NOT EXISTS</code>:

<pre><code>SELECT r.*
FROM Room r LEFT JOIN
WHERE NOT EXISTS (SELECT 1
 FROM Busy b
 WHERE r.RoomdId = b.RoomId AND
 @dateArr &lt;= b.DepartureDate AND
 @dateDep &gt;= b.ArrivalDate
 );
</code></pre>

blocks|key|3072061|text|如果能看到更多的表列，将会很有帮助。你可能想要考虑重构它们--它们看起来并不是第三种正常形式。另外，还不清楚繁忙的桌子是否包含了所有租房时间的全部历史。如果繁忙只包含当前的预订，则此操作应该有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3072062|从“繁忙”中选择“IdRoom”，并选择“>=+DepartureDate”和@DepartureDate+<=+ArrivalDate|3072063|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It would be helpful to see a few more columns of your tables. And you may want to consider refactoring them - They don't appear to be in 3rd normal form. Also its not clear if the BUSY table contains the entire history of all the times the rooms were rented. This should work if BUSY contains only current reservations:

SELECT IdRoom 
FROM BUSY
WHERE @ArrivalDate >= DepartureDate
AND @DepartureDate &lt;= ArrivalDate

blocks|key|3072092|text|有4种情况下，日期比较应该说房间是繁忙的。@Simonare在正确的轨道上选择了繁忙的房间，然后将它们排除在主SELECT+*+FROM+Room之外。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3072093|我的小表格展示了4种失败场景：|3072094|12345678+->+Eight+Dates+in+a+row.
..BBBB..+->+Assume:+These+four+Dates+are+'B'+busy.+'D'+dates+below+are+desired/requested.+

.D......+->+Success:+Desired+is+before+busy+dates
......D.+->+Success:+Desired+is+after+busy+dates
..DD....+->+Fails:+Desired+within+busy+dates
.DD.....+->+Fails:+Desired+overlaps+starting+before+beginning+of+busy+dates
.....DD.+->+Fails:+Desired+overlaps+extending+after+ending+of+busy+dates
.DDDDDD.+->+Fails:+Desired+extends+both+before+and+after+busy+dates|code-block|syntax|javascript|3072095|这些是删除失败的WHERE+子句：|BOLD|3072096|繁忙日期内的请求：繁忙到达日期AT或之后的繁忙到达日期和繁忙的出发日期LE期望的起飞日期|3072097|SELECT+'Busy.DateArr'+<=+@dateArr+AND++'Busy.DateDep'+>=+@dateDep|3072098|请求在繁忙开始之前启动:期望到达日期在繁忙到达日期之前，所期望的出发日期在繁忙到达日期之后)|3072099|SELECT+@dateArr+<+'Busy.DateArr'+AND+@dateDep+>+'Busy.DateArr'|3072100|请求在繁忙结束之后结束:所期望的到达日期在繁忙的出发日期之前，所期望的出发日期在繁忙的出发日期之后。|3072101|SELECT+@dateArr+<+'Busy.DateDep'+AND+@dateDep+>+'Busy.DateDep'|3072102|请求超出开始和结束：期望到达日期在繁忙到达日期之前，期望出发日期在繁忙离开日期之后|3072103|SELECT+@dateArr+<+'Busy.DateArr'+AND++@dateDep+>+'Busy.DateDep'|3072104|使整个SQL变成.|3072105|SELECT+*+FROM+Room
++WHERE+NOT+EXISTS+(+
+++SELECT+1+FROM+Busy+WHERE+Room.RoomdId=+Busy.RoomdId+AND
+++((@dateArr>=+'Busy.DateArr'+AND+@dateDep<=+'Busy.DateDep')+OR
++++(@dateArr+<+'Busy.DateArr'+AND+@dateDep+>+'Busy.DateArr')+OR
++++(@dateArr+<+'Busy.DateDep'+AND+@dateDep+>+'Busy.DateDep')+OR
++++(@dateArr+<+'Busy.DateArr'+AND+@dateDep+>+'Busy.DateDep')+)
+)|3072106|很抱歉，我还没有真正运行这个程序，所以由于复杂性，可能会出现逻辑或语法错误。您可以单独测试和调试每个选择部分，以确保它们是正确的。此外，明智地使用一个小布尔代数可以大大简化这一说法。|3072107|还有..。我假设有人可以在出发日期到达。|3072108|entityMap^0|1J|I|0|0|0|8|5|E|3|8|5|0|0|8|0|0|0|7|0|0|0|7|0|0|0|A|0|0|0|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1I|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1J|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1K|8|@$9|1L|A|1M|B|O]|$9|1N|A|1O|B|O]|$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1R|8|@$9|1S|A|1T|B|O]]|D|@]|E|$]]|$1|R|3|S|5|J|7|1U|8|@]|D|@]|E|$K|L]]|$1|T|3|U|5|6|7|1V|8|@$9|1W|A|1X|B|O]]|D|@]|E|$]]|$1|V|3|W|5|J|7|1Y|8|@]|D|@]|E|$K|L]]|$1|X|3|Y|5|6|7|1Z|8|@$9|20|A|21|B|O]]|D|@]|E|$]]|$1|Z|3|10|5|J|7|22|8|@]|D|@]|E|$K|L]]|$1|11|3|12|5|6|7|23|8|@$9|24|A|25|B|O]]|D|@]|E|$]]|$1|13|3|14|5|J|7|26|8|@]|D|@]|E|$K|L]]|$1|15|3|16|5|6|7|27|8|@$9|28|A|29|B|O]]|D|@]|E|$]]|$1|17|3|18|5|J|7|2A|8|@]|D|@]|E|$K|L]]|$1|19|3|1A|5|6|7|2B|8|@]|D|@]|E|$]]|$1|1B|3|1C|5|6|7|2C|8|@]|D|@]|E|$]]|$1|1D|3|-4|5|6|7|2D|8|@]|D|@]|E|$]]]|1E|$]]

There are 4 scenarios where the date comparisons should say the room is busy. @Simonare was on the right track by using a sub-selection for the busy rooms, and then excluding them from the main <code>SELECT * FROM Room</code>

My kludgy little table illustrates the 4 failure scenarios:

<pre><code>12345678 -&gt; Eight Dates in a row.
..BBBB.. -&gt; Assume: These four Dates are 'B' busy. 'D' dates below are desired/requested. 

.D...... -&gt; Success: Desired is before busy dates
......D. -&gt; Success: Desired is after busy dates
..DD.... -&gt; Fails: Desired within busy dates
.DD..... -&gt; Fails: Desired overlaps starting before beginning of busy dates
.....DD. -&gt; Fails: Desired overlaps extending after ending of busy dates
.DDDDDD. -&gt; Fails: Desired extends both before and after busy dates
</code></pre>

These are the <code>WHERE</code> clauses to remove the failures:

Request within busy dates: The Busy arrival date AT or AFTER desired arrival date AND Busy departure date LE desired departure date

<pre><code>SELECT 'Busy.DateArr' &lt;= @dateArr AND 'Busy.DateDep' &gt;= @dateDep
</code></pre>

Request starts before Busy beginning: The desired arrival date is BEFORE the Busy arrival date AND the desired departure date is AFTER the Busy arrival date)

<pre><code>SELECT @dateArr &lt; 'Busy.DateArr' AND @dateDep &gt; 'Busy.DateArr'
</code></pre>

Request ends After Busy ending: The desired arrival date is BEFORE Busy departure date AND the desired departure date is AFTER the Busy departure date

<pre><code>SELECT @dateArr &lt; 'Busy.DateDep' AND @dateDep &gt; 'Busy.DateDep'
</code></pre>

Request extending beyond both begin &amp; end: The desired arrival date is BEFORE Busy arrival date AND desired departure date is AFTER Busy departure date

<pre><code>SELECT @dateArr &lt; 'Busy.DateArr' AND @dateDep &gt; 'Busy.DateDep'
</code></pre>

So the entire SQL becomes...

<pre><code>SELECT * FROM Room
 WHERE NOT EXISTS ( 
 SELECT 1 FROM Busy WHERE Room.RoomdId= Busy.RoomdId AND
 ((@dateArr&gt;= 'Busy.DateArr' AND @dateDep&lt;= 'Busy.DateDep') OR
 (@dateArr &lt; 'Busy.DateArr' AND @dateDep &gt; 'Busy.DateArr') OR
 (@dateArr &lt; 'Busy.DateDep' AND @dateDep &gt; 'Busy.DateDep') OR
 (@dateArr &lt; 'Busy.DateArr' AND @dateDep &gt; 'Busy.DateDep') )
 )
</code></pre>

Apologies, I haven't actually run this, so with the complexity, there may be a logic or syntax error. You can test and debug each SELECT portion individually to ensure they are correct. Also, the judicious use of a little Boolean Algebra could significantly simplify the statement.

And ... I am assuming someone can arrive on a departure date.

blocks|key|3072128|text|好的，我已经更正了我的疑问。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3072129|这是我使用的查询：|3072130|SELECT+*+
FROM+Room+
WHERE+RoomId+in+(
+++++++++++++SELECT+RoomID+FROM+Reservation+WHERE+EndDate+<=+@startDate+OR++
+++++++++++++StartDate+>=+@endDate
+++++++++++++)|code-block|syntax|javascript|3072131|当然有一个更好的方法来做，但它做的工作。(谢谢你们帮助我;)|3072132|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Ok so i've corrected my query.

That's the query i use:

<pre><code>SELECT * 
FROM Room 
WHERE RoomId in (
 SELECT RoomID FROM Reservation WHERE EndDate &lt;= @startDate OR 
 StartDate &gt;= @endDate
 )
</code></pre>

There is surely a better way to do it but it does the job.
Thank you all for helping me ;)

Hi!

I have two tables, "room" and "busy". My busy table contains reservation records containing an arrival date and a departure date.

I wish to make a room selection that is not booked for two dates given.

Someone gives me the date to which he wants to reserve his room and to which he intends to leave, from that I want to be able to show the rooms that are available to him.

Busy Table => |IdRoom||ArrivalDate|DepartureDate|

Room Table => |IdRoom|NumRoom|And so on..|

I tried different queries but I don't get anything.

My tested query:

<pre><code>SELECT Room.*, Busy.* 
FROM Room
INNER JOIN Busy ON Room.RoomdId= Busy.RoomdId
WHERE (@dateArr NOT BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate') 
AND (@dateDep NOT BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate')
</code></pre>

Thanks for your help in advance!

Select between two date

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

嗨！我有两张桌子，“房间”和“忙”。我的座位上有一个到达日期和一个出发日期的预订记录。我想选择两次约会没有预约的房间。有人告诉我他想预订房间的日期和他打算离开的日期，这样我就能展示给他可用的房间了。繁忙的桌子，=>，IdRoom，，，IdRoom，IdRoom，IdRoom，IdRoom，IdRoom，IdRoom，I...

问在两个日期之间选择
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在两个日期之间选择EN