SQLITE选择与加入一起选择仅在第二个表中的每个行中的每个行中的每个条款中都需要返回行

发布于 2025-02-02 00:48:51 字数 877 浏览 2 评论 0原文

我需要选择要空置，即使仅第二个表中的一排不符合日期条件。，但是每个停车位在预订表中都有多个预订，即使其中一个预订符合不在范围内的标准我将获得停车位的身份证。

我的选择

SELECT Parking_Spaces.ID_Parking_Space FROM Reservations 

INNER JOIN Parking_Spaces ON Reservations.ID_Parking_space = Parking_Spaces.ID_Parking_Space
AND (Reservations.Arrival_time NOT BETWEEN "2000-12-15" AND "2005-12-15")
AND (Reservations.Departure_time NOT BETWEEN "2000-12-15" AND "2005-12-15")

表示例：我不想从此表中选择ID_Parking_Space，因为第一行不符合我的状况。但是因为第二个我确实得到了ID。

|ID_RESERVATION |  Arrival_time  |    Departure_time   |  ID_Parking_Space
|:------------- |:--------------:|: ------------------:|:----------------:|
|      6        |   2001-12-15   |    2002-12-15       |        4         |
|      16       |   2009-12-15   |    2010-12-15       |        4         |

在整个表中，有更多的预订，如果一行不符合条件，则我需要选择要失败。

原文

I need the select to be empty even if only one row in the second table doesn't meet the dates condition. But each parking space has multiple reservations in Reservations table and even if one of the reservation meets the criteria of not being in the range I will get the ID of parking space.

My SELECT

SELECT Parking_Spaces.ID_Parking_Space FROM Reservations 

INNER JOIN Parking_Spaces ON Reservations.ID_Parking_space = Parking_Spaces.ID_Parking_Space
AND (Reservations.Arrival_time NOT BETWEEN "2000-12-15" AND "2005-12-15")
AND (Reservations.Departure_time NOT BETWEEN "2000-12-15" AND "2005-12-15")

Table example: I don't want to SELECT ID_Parking_Space from this table, because first row doesn't meet my condition. But because second one does I do get the ID.

|ID_RESERVATION |  Arrival_time  |    Departure_time   |  ID_Parking_Space
|:------------- |:--------------:|: ------------------:|:----------------:|
|      6        |   2001-12-15   |    2002-12-15       |        4         |
|      16       |   2009-12-15   |    2010-12-15       |        4         |

In the full table there are more reservations and if one row doesn't meet the condition I need the select to fail.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦里的微风 2025-02-09 00:48:52

我认为您需要的是不存在：

SELECT p.ID_Parking_Space 
FROM Parking_Spaces p 
WHERE NOT EXISTS (
  SELECT 1
  FROM Reservations r 
  WHERE r.ID_Parking_space = p.ID_Parking_Space
  AND (
    r.Arrival_time BETWEEN '2000-12-15' AND '2005-12-15' 
    OR 
    r.Departure_time BETWEEN '2000-12-15' AND '2005-12-15'
  )
);

I think what you need is NOT EXISTS:

SELECT p.ID_Parking_Space 
FROM Parking_Spaces p 
WHERE NOT EXISTS (
  SELECT 1
  FROM Reservations r 
  WHERE r.ID_Parking_space = p.ID_Parking_Space
  AND (
    r.Arrival_time BETWEEN '2000-12-15' AND '2005-12-15' 
    OR 
    r.Departure_time BETWEEN '2000-12-15' AND '2005-12-15'
  )
);

回复收藏 0 原文

~没有更多了~