SQL 查询不存在，存在

Question

模式

Movie(title, year, director, budget, earnings)
Actor(stagename, realname, birthyear)
ActedIn(stagename, title, year, pay)
CanWorkWith(stagename, director)

我需要找到所有从未在盈利的电影中工作过的 Actor (艺名和实名)(收入 > 预算)。所以找到所有的坏 Actor :P

SELECT A.stagename, A.realname
FROM Actor A
WHERE A.stagename NOT IN
   (SELECT B.stagename
    FROM ActedIN B
    WHERE EXIST 
         (SELECT *
          FROM Movie M
          WHERE M.earnings > M.budget AND M.title = B.title AND M.year))

这会找到所有艺名没有出现在第二个查询中的 Actor 吗？第二个查询将查找在盈利的电影中出演的所有艺名。

这样对吗？

Answer 1

我想你可以简化一下，见下文:

SELECT DISTINCT A.stagename, A.realname
FROM Actor A
WHERE NOT EXISTS
  (SELECT *
     FROM Actor B
        , Movie M
        , ActedIn X
    WHERE M.Title = X.Title
      AND X.StageName = B.StageName
      AND M.earnings > M.budget
      AND M.year = X.Year
      AND A.StageName = B.StageName)