从NOT EXISTS转换为NOT IN

一尘不染

从NOT EXISTS转换为NOT IN

sql

我有三个表：

sailor （Name, grade）；
boat （Name, color, grade）；
reservation （sname，bname，Weekdays, start, end）；

为了获得保留每艘红船的水手的清单，我有：

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（））。

任何帮助表示赞赏

阅读 270

2021-03-08

共1个答案

一尘不染

为了得到保留每条船的水手名单。我将使用此脚本

解决方案1：

 ;WITH k AS 
    (
    SELECT b.sname,COUNT(distinct a.bname) coun FROM boat a
    INNER JOIN reservation b 
        on a.bname = b.bname
    GROUP BY b.sname
    )
    SELECT k.sname FROM k WHERE coun = (select COUNT(*) FROM boat AS b)

解决方案2：

SELECT s.sname
FROM   sailor AS s
WHERE  s.sname NOT IN (SELECT DISTINCT a.sname
                       FROM   (SELECT s.sname,
                                      b.bname
                               FROM   sailor AS s
                                      CROSS JOIN boat AS b
                               WHERE  b.color = "Red") a
                       WHERE  a.sname + a.bname 
                                            NOT IN (SELECT r.sname + r.bname
                                                    FROM   reservation AS r
                                                    WHERE  r.sname IS NOT NULL
                                                            AND r.bname IS NOT NULL));

2021-03-08