从NOT EXISTS转换为NOT INIS [英] Converting From NOT EXISTS to NOT IN

问题描述

我有三个表：

• sailor （sname，rating）


• boat （bname，color，rating）;


• 预订（sname，bname，工作日，开始，完成）;

• sailor (sname, rating);
• boat (bname, color, rating);
• reservation (sname, bname, weekday, start, finish);

为了得到一个保留每艘红船的水手名单，我有：

In order to get a list of sailors who have reserved every red boat, I have:

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重写这个查询。这是我到目前为止：

I now need to rewrite this query with NOT IN instead of NOT EXISTS. Here's what I have so far:

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

This, however, returns a list of all sailors who have reserved a red boat (not necessarily all of them). I'm having great difficulty checking whether or not a name in the list has reserved EVERY boat (I also cannot use COUNT()).

感谢任何帮助

推荐答案

水手谁保留每艘船。我将使用此脚本

Inorder to get a list of sailors who have reserved every boat. I'll use this script

解决方案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));

这篇关于从NOT EXISTS转换为NOT INIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！