In the following relational schema, how can I derive the available "Basic" types of rooms which are not booked from a DATE RANGE. This is my shot. I am working with oracle in sqlplus
在以下关系模式中,如何导出未在日期范围内预订的可用“基本”类型的房间。这是我的镜头。我在sqlplus中使用oracle
SELECT *
FROM ROOM r, BOOKING b
WHERE NOT EXISTS
(SELECT * FROM BOOKINGROOM br
WHERE br.ROOMNO = r.ROOMNO
AND br.BOOKINGID = b.BOOKINGID
AND ARRIVEDATE < '01-FEB-2013'
AND DEPARTDATE > '23-FEB-2013');
I also want the query to be 'canned query' so I manually add the end range and start range dates. Sub query answer would be preferred.
我还希望查询为“固定查询”,因此我手动添加结束范围和开始范围日期。子查询答案将是首选。
INSERT INTO BOOKING VALUES (2314, 1001, TO_DATE('10-MAR-2013', 'DD-MON-YYYY'), TO_DATE('15-MAR-2013', 'DD-MON-YYYY'), 1225.00);
Date comparison has been an issue perhaps in the below answers.
日期比较可能是以下答案中的一个问题。
2 个解决方案
#1
2
The simplest approach would be:
最简单的方法是:
SELECT DISTINCT r.*
FROM Room r
LEFT JOIN BookingRoom br ON r.FloorNo = br.FloorNo AND r.RoomNo = br.RoomNo
LEFT JOIN Booking b ON br.BookingId = b.BookingId
AND (b.ArriveDate <= &end_range AND b.DepartDate >= &start_range)
WHERE b.BookingId IS NULL
AND r.Type = 'BASIC';
If you absolutely must use a subquery, try this:
如果您绝对必须使用子查询,请尝试以下操作:
SELECT DISTINCT r.*
FROM Room r
LEFT JOIN BookingRoom br ON r.FloorNo = br.FloorNo AND r.RoomNo = br.RoomNo
LEFT JOIN Booking b ON br.BookingId = b.BookingId
WHERE (b.BookingId IS NULL OR b.BookingId NOT IN (
SELECT BookingId
FROM Booking
WHERE (ArriveDate <= &end_range AND DepartDate >= &start_range)
)) AND r.Type = 'BASIC';
#2
1
Try this query, which will detect conflicting bookings whether they are enclosing, enclosed by, or partly overlapping with the desired date range.
尝试此查询,它将检测冲突的预订,无论它们是否包含,封闭或部分与所需的日期范围重叠。
SELECT DISTINCT r.*
FROM Room r
LEFT JOIN BookingRoom br ON r.FloorNo = br.FloorNo AND r.RoomNo = br.RoomNo
LEFT JOIN Booking b ON br.BookingId = b.BookingId
AND b.ArriveDate < &end_range AND b.DepartDate > &start_range
WHERE b.BookingId IS NULL
AND r.Type = 'BASIC';
#1
2
The simplest approach would be:
最简单的方法是:
SELECT DISTINCT r.*
FROM Room r
LEFT JOIN BookingRoom br ON r.FloorNo = br.FloorNo AND r.RoomNo = br.RoomNo
LEFT JOIN Booking b ON br.BookingId = b.BookingId
AND (b.ArriveDate <= &end_range AND b.DepartDate >= &start_range)
WHERE b.BookingId IS NULL
AND r.Type = 'BASIC';
If you absolutely must use a subquery, try this:
如果您绝对必须使用子查询,请尝试以下操作:
SELECT DISTINCT r.*
FROM Room r
LEFT JOIN BookingRoom br ON r.FloorNo = br.FloorNo AND r.RoomNo = br.RoomNo
LEFT JOIN Booking b ON br.BookingId = b.BookingId
WHERE (b.BookingId IS NULL OR b.BookingId NOT IN (
SELECT BookingId
FROM Booking
WHERE (ArriveDate <= &end_range AND DepartDate >= &start_range)
)) AND r.Type = 'BASIC';
#2
1
Try this query, which will detect conflicting bookings whether they are enclosing, enclosed by, or partly overlapping with the desired date range.
尝试此查询,它将检测冲突的预订,无论它们是否包含,封闭或部分与所需的日期范围重叠。
SELECT DISTINCT r.*
FROM Room r
LEFT JOIN BookingRoom br ON r.FloorNo = br.FloorNo AND r.RoomNo = br.RoomNo
LEFT JOIN Booking b ON br.BookingId = b.BookingId
AND b.ArriveDate < &end_range AND b.DepartDate > &start_range
WHERE b.BookingId IS NULL
AND r.Type = 'BASIC';