为什么我的SQL脚本选择了太多的输出(CS50 pset7 SQL.13)

Question

我目前正在cs50中处理pset7，我认为我已经选择了正确数量的指定值，但我的脚本输出的是349行，而不是答案键的176行。

"在13.sql中，编写一个sql查询，列出凯文·培根主演的电影中所有主演的人的名字。您的查询应该输出一个表，其中每个人的名字只有一列。数据库中可能有多个名叫凯文·培根的人。请务必只选择1958年出生的凯文·培根。凯文·培根本人不应被列入最终名单">

CS50 pset7 sql.13

sqlite> .schema
CREATE TABLE movies (
id INTEGER,
title TEXT NOT NULL,
year NUMERIC,
PRIMARY KEY(id)
);
CREATE TABLE stars (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE directors (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE ratings (
movie_id INTEGER NOT NULL,
rating REAL NOT NULL,
votes INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id)
);
CREATE TABLE people (
id INTEGER,
name TEXT NOT NULL,
birth NUMERIC,
PRIMARY KEY(id)
);

我的脚本

SELECT DISTINCT name
FROM 
people
INNER JOIN  stars ON people.id = stars.person_id
INNER JOIN movies ON movies.id = stars.movie_id
WHERE movies.title IN (
SELECT
title

From
movies
INNER JOIN stars ON movies.id = stars.movie_id
INNER JOIN people ON stars.person_id= people.id

WHERE 
people.name = "Kevin Bacon"
AND
people.birth = "1958"
)

EXCEPT SELECT name FROM people WHERE people.name = "Kevin Bacon"

这个脚本中有一些逻辑错误吗？我的逻辑是：

• 选择凯文·培根主演的所有电影(嵌套Select(
• 选择凯文·培根电影中出现的明星的名字(主选择(，凯文·培根本人除外

Answer 1

这样的东西在postgres中也能工作。可能需要适应您的数据库。

select name
from (
with kb_movies as
(select distinct movies.id as kb_movie_id
from movies
join stars 
on stars.movie_id = movies.id
join people 
on people.id = stars.people_id
where people.name = 'Kevin Bacon'
and people.birth = '1958' --or 1958
)
select distinct people.name
from people
join stars 
on stars.people_id = people.id
join movies 
on movies.id = stars.movie_id
join kb_movies 
on kb_movie_id = movies.id
)z
where name <> 'Kevin Bacon'