sql - 从 NOT EXISTS 转换为 NOT IN

Question

我有三个表:

• sailor(名字，等级)；
• boat(名称、颜色、评级)；
• 预订(姓名、姓名、工作日、开始、结束)；

为了获得预订每艘红船的水手名单，我有:

select s.sname from sailor s 
where not exists(  
    select * from boat b  
    where b.color = 'red'  
    and not exists (  
        select * from reservation r  
        where r.bname = b.bname  
        and r.sname = s.sname));

我现在需要用 NOT IN 而不是 NOT EXISTS 重写这个查询。这是我到目前为止所拥有的:

select s.sname from sailor s
where s.sname not in (select s2.sname from sailor s2 where
    s2.sname not in (select r.sname from reservation r where r.bname not in (
            select b.bname from boat b where b.color <> 'red' )));

但是，这将返回所有预订了红色船的水手的列表(不一定是所有水手)。我很难检查列表中的名称是否保留了每艘船(我也不能使用 COUNT())。

感谢任何帮助

Answer 1

这很有趣；您可以在将 NOT EXISTS ( ... ) 替换为 IN ( ...) 时保持句法结构(相关子查询):

SELECT s.sname from sailor s
WHERE 13 NOT IN (
    SELECT 13 FROM boat b
    WHERE b.color = 'red'
    AND 42 NOT IN (
        SELECT 42 from reservation r
        WHERE r.bname = b.bname
        AND r.sname = s.sname
       )
    );