我有两个表:
有这样一张桌子的:表名预订:
Name Room Seats Date
John 101 20 11/22
桌名室:
Name Seats
101 30
202 40
SELECT *
FROM Reservations
WHERE date = '2020-11-22' AND
SELECT Reservations.Room
FROM Reservations
WHERE Reservations.Room NOT IN(SELECT Room.Name FROM Room)
我尝试过上面的语句,但它不起作用。我希望命令返回日期内未使用的所有房间。它应该返回202房间。就是这样。
我知道您想要在指定日期有座位的房间。一个选项使用子查询进行筛选:
select ro.*
from rooms ro
where ro.seats > (
select coalesce(sum(re.seats), 0)
from reservations re
where re.room = ro.name and re.date = '2020-11-12'
)
另一方面,如果您想要在指定日期根本没有预订的房间,请使用not exists
:
select ro.*
from rooms ro
where not exists (
select 1
from reservations re
where re.room = ro.name and re.date = '2020-11-12'
)