从NOT EXISTS转换为NOT IN

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

解决方案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.snameFROM   sailor AS sWHERe  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));