mysql - 找出所有与克里斯托弗·诺兰合作的电影比其他任何导演都多的演员

Question

我需要找到与诺兰合作的电影比其他任何导演都多的演员。

“这个查询会给我克里斯托弗诺兰导演的电影”

  select P1.PID, P1.Name,
   Movie.MID 
   from Person P1 natural join M_Cast natural 
  join Movie join M_Director on (Movie.MID = M_Director.MID) join Person P2 on (M_Director.PID = P2.PID) where P2.Name='Christopher Nolan'

https://i.stack.imgur.com/I2NAp.jpg

标签： mysqlsql

Answer 1

我并没有真正测试它，但类似的东西应该可以工作：

SELECT *
FROM   (
        SELECT   actor.name AS ActorName,
                 COUNT(CASE UPPER(dirct.name) WHEN 'CHRISTOPHER NOLAN' THEN 1 ELSE NULL END) AS WithNolan,
                 COUNT(CASE UPPER(dirct.name) WHEN 'CHRISTOPHER NOLAN' THEN NULL ELSE 1 END) AS NotNolan
        FROM     movie
        JOIN     m_cast
          ON     movie.mid = m_cast.mid
        JOIN     person actor
          ON     m_cast.pid = actor.pid
        JOIN     m_director
          ON     movie.mid = m_director.mid
        JOIN     person dirct
          ON     m_director.pid = dirct.pid
        GROUP BY actor.name
       )
WHERE  WithNolan > NotNolan;