使用 MySQL 从 IMDB 数据库查询

考虑以下：

DROP TABLE IF EXISTS person;

CREATE TABLE person
(person_id SERIAL PRIMARY KEY
,name VARCHAR(20) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS movie;

CREATE TABLE movie
(movie_id SERIAL PRIMARY KEY
,title VARCHAR(50) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS m_cast;

CREATE TABLE m_cast
(movie_id INT NOT NULL
,person_id INT NOT NULL
,PRIMARY KEY(movie_id,person_id)
);

DROP TABLE IF EXISTS m_director;

CREATE TABLE m_director
(movie_id INT NOT NULL
,person_id INT NOT NULL
,PRIMARY KEY(movie_id,person_id)
);

INSERT INTO person (name) VALUES
('Steven Feelberg'),
('Manly Kubrick'),
('Alfred Spatchcock'),
('Fred Pitt'),
('Raphael DiMaggio'),
('Bill Smith');

INSERT INTO movie VALUES
(1,'Feelberg\'s Movie with Fred & Raph'),
(2,'Feelberg and Fred Ride Again'),
(3,'Kubrick shoots DiMaggio'),
(4,'Kubrick\'s Movie with Bill Smith'),
(5,'Spatchcock Presents Bill Smith');

INSERT INTO m_director VALUES
(1,1),
(2,1),
(3,2),
(4,2),
(5,3);

INSERT INTO m_cast VALUES
(1,4),
(1,5),
(2,4),
(3,5),
(4,6),
(5,6);

我包括电影表只是为了便于参考。它与实际问题无关。另请注意，此模型假设演员只列出一次，无论他们是否在给定的电影中担任多个角色。

以下查询询问“每个演员和导演合作的频率”...

演员是任何曾在任何电影中担任演员的人。导演是曾担任任何电影导演的任何人。

SELECT a.name actor
     , d.name director
     , COUNT(DISTINCT ma.movie_id) total
  FROM person d
  JOIN m_director md 
    ON md.person_id = d.person_id
  JOIN person a
  LEFT
  JOIN m_cast ma 
    ON ma.person_id = a.person_id
   AND ma.movie_id = md.movie_id
  JOIN m_cast x
    ON x.person_id = a.person_id
 GROUP
    BY actor
     , director;
     
+-------------------+-------------------+-------+
| actor             | director          | total |
+-------------------+-------------------+-------+
| Fred Pitt         | Alfred Spatchcock |     0 |
| Fred Pitt         | Manly Kubrick     |     0 |
| Fred Pitt         | Steven Feelberg   |     2 |
| Raphael DiMaggio  | Alfred Spatchcock |     0 |
| Raphael DiMaggio  | Manly Kubrick     |     1 |
| Raphael DiMaggio  | Steven Feelberg   |     1 |
| Bill Smith        | Alfred Spatchcock |     1 |
| Bill Smith        | Manly Kubrick     |     1 |
| Bill Smith        | Steven Feelberg   |     0 |
+-------------------+-------------------+-------+

通过观察，我们可以看出：

• 唯一一个比其他导演更经常与费尔伯格合作的演员是弗雷德·普里特
• 拉斐尔·迪卡普里奥 (Raphael DiCaprio) 和比尔·史密斯 (Bill Smith) 与两位导演（尽管导演不同）的合作频率相同

编辑：虽然我并没有认真提倡将其作为解决方案，但以下只是为了证明上面提供的内核确实是解决问题所需的全部内容......

SELECT x.*
  FROM 
     ( SELECT a.* 
 FROM 
    ( SELECT a.name actor
           , d.name director
           , COUNT(DISTINCT ma.movie_id) total
        FROM person d
        JOIN m_director md 
          ON md.person_id = d.person_id
        JOIN person a
        LEFT
        JOIN m_cast ma 
          ON ma.person_id = a.person_id
         AND ma.movie_id = md.movie_id
        JOIN m_cast x
          ON x.person_id = a.person_id
       GROUP
          BY actor
           , director
    ) a
 LEFT
 JOIN
    ( SELECT a.name actor
           , d.name director
           , COUNT(DISTINCT ma.movie_id) total
        FROM person d
        JOIN m_director md 
          ON md.person_id = d.person_id
        JOIN person a
        LEFT
        JOIN m_cast ma 
          ON ma.person_id = a.person_id
         AND ma.movie_id = md.movie_id
        JOIN m_cast x
          ON x.person_id = a.person_id
       GROUP
          BY actor
           , director
    ) b
   ON b.actor = a.actor
  AND b.director <> a.director 
  AND b.total > a.total
WHERE b.actor IS NULL
) x
LEFT JOIN
     ( SELECT a.* 
 FROM 
    ( SELECT a.name actor
           , d.name director
           , COUNT(DISTINCT ma.movie_id) total
        FROM person d
        JOIN m_director md 
          ON md.person_id = d.person_id
        JOIN person a
        LEFT
        JOIN m_cast ma 
          ON ma.person_id = a.person_id
         AND ma.movie_id = md.movie_id
        JOIN m_cast x
          ON x.person_id = a.person_id
       GROUP
          BY actor
           , director
    ) a
 LEFT
 JOIN
    ( SELECT a.name actor
           , d.name director
           , COUNT(DISTINCT ma.movie_id) total
        FROM person d
        JOIN m_director md 
          ON md.person_id = d.person_id
        JOIN person a
        LEFT
        JOIN m_cast ma 
          ON ma.person_id = a.person_id
         AND ma.movie_id = md.movie_id
        JOIN m_cast x
          ON x.person_id = a.person_id
       GROUP
          BY actor
           , director
    ) b
   ON b.actor = a.actor
  AND b.director <> a.director 
  AND b.total > a.total
WHERE b.actor IS NULL
) y
ON y.actor = x.actor AND y.director <> x.director
WHERE y.actor IS NULL;

+-----------+-----------------+-------+
| actor     | director        | total |
+-----------+-----------------+-------+
| Fred Pitt | Steven Feelberg |     2 |
+-----------+-----------------+-------+

这将返回每个演员的列表，以及他们最常合作的导演。在这种情况下，因为 Bill Smith 和 Raphael DiMaggio 最常与两位导演平等合作，所以他们被排除在结果之外。

解决您的问题的方法很简单，就是从该列表中选择 Yash Chopra 被列为主管的所有行。