试图输出演员角色时得到错误答案

Question

我正在想办法获取Daniel Radcliffe的职业历史。然而，我得到的结果与Daniel Radcliffe无关。

CREATE TABLE person (
id integer primary key,
name text not null
);
CREATE TABLE movie (
id integer primary key,
name text not null
);
CREATE TABLE casts (
movie_id integer not null,
person_id integer not null,
played_as text not null,
foreign key (movie_id) references movie(id),
foreign key (person_id) references person(id)
);
CREATE TABLE crew (
movie_id integer not null,
person_id integer not null,
job text not null,
foreign key (movie_id) references movie(id),
foreign key (person_id) references person(id)
);
CREATE TABLE famous_level (
movie_id integer not null,
person_id integer not null,
level integer not null,
foreign key (movie_id) references movie(id),
foreign key (person_id) references person(id)
);
INSERT INTO person (id, name) VALUES (1, 'Daniel Radcliffe'), (2, 'Emma Watson'), (3, 'Robert Downey Jr.'), (4, 'Joss Whedon');
INSERT INTO movie (id, name) VALUES (1, 'Harry Potter movie'), (2, 'The Avengers');
INSERT INTO casts (movie_id, person_id, played_as) VALUES (1, 1, 'Harry Potter'), (1, 2, 'Hermione Granger'), (2, 3, 'Tony Stark');
INSERT INTO crew (movie_id, person_id, job) VALUES (1, 1, 'Writer'), (1, 4, 'Director'), (2, 2, 'Director'), (2, 1, 'Writer');
INSERT INTO famous_level (movie_id, person_id, level) VALUES (1, 1, 1), (1, 2, 3), (2, 3, 2), (1, 4, 2);

---

我知道Daniel Radcliffe的ID是1

SELECT
m.name,
ct.played_as,
cr.job
FROM movie m
JOIN crew cr ON m.id = cr.movie_id
JOIN casts ct ON m.id = ct.movie_id
JOIN famous_level f ON m.id = f.movie_id
JOIN person p ON cr.person_id = p.id
WHERE p.id = 1
ORDER BY f.level ASC

我想得到类似的结果，其中played_as或job列上有NULL值

+--------------------+--------------+--------+
|        name        |  played_as   |  job   |
+--------------------+--------------+--------+
| Harry Potter movie | Harry Potter | NULL   |
| Harry Potter movie | NULL         | Writer |
| The Avengers       | NULL         | Writer |
+--------------------+--------------+--------+

我得到的结果：http://www.sqlfiddle.com/#！17/28dd0

Answer 1

因为您想为每个电影的每个played_as或job获得一行，所以您需要创建一个包含所有played_as和job值的表，这可以通过UNION查询来完成。当played_as有效时，该查询为job返回NULL，反之亦然。然后，它可以被JOIN转换到movie、person和famous_level表，以在person_id上进行过滤，并按level:排序

SELECT m.name,
r.played_as,
r.job
FROM movie m
JOIN (
SELECT movie_id, person_id, played_as, NULL as job
FROM casts
UNION ALL 
SELECT movie_id, person_id, NULL, job
FROM crew
) r ON r.movie_id = m.id
JOIN person p ON p.id = r.person_id
LEFT JOIN famous_level f ON f.movie_id = m.id AND f.person_id = p.id
WHERE p.id = 1
ORDER BY COALESCE(f.level, 99)

输出：

name                played_as       job
Harry Potter movie  (null)          Director
Harry Potter movie  (null)          Writer
Harry Potter movie  Harry Potter    (null)
The Avengers        (null)          Writer

SQLFiddle 演示

请注意，您需要在movie_id和person_id上同时使用JOIN到famous_level，以避免在输出中获得额外的行。由于并非所有电影/人物组合都有famous_level，因此当level值为NULL时，需要在其上使用LEFT JOIN和COALESCE。