cs50 pset7 13.sql-从结果中删除kevin [英] cs50 pset7 13.sql - remove kevin from results

问题描述

更新:通过更改2件事，我能够轻松，简单地解决此问题:

UPDATE: I was able to easily and simply solve this problem by changing 2 things:

1. 添加到代码末尾: AND people.name！=凯文培根"
2. 将嵌套查询更改为movies.id，而不是标题.

感谢此主题提供答案:

Thanks to this thread for the answer: CS50 Pset 7 13.sql, I can't solve it, nested sqlite3 database

我正在PSET7电影的最后一步( https://cs50.harvard.edu/x/2020/psets/7/movies/)目标输出应该是与凯文一起出演过电影的所有人，但凯文本人不应该包括在内.

I'm at the very last step of PSET7 movies (https://cs50.harvard.edu/x/2020/psets/7/movies/) The goal output should be all people that have starred in a movie with Kevin, but Kevin himself should NOT be included.

我有一个查询，该查询返回与凯文·培根一起出演电影的人的所有名字的列表，但凯文在那儿.如何轻松将他从结果中删除?我已经研究了NOT IN，但是我不知道如何以设置方式将其用于嵌套查询.因此，任何建议都将不胜感激.

I have a query that returns a list of all the names of people who starred in movies with Kevin Bacon, but Kevin is there. How can I remove him from the results easily? I've looked into NOT IN, but I can't figure out how to get that to work with nested queries the way I have set this up. So any advice is greatly appreciated.

这是我的代码:

SELECT DISTINCT people.name
FROM people
INNER JOIN stars ON stars.person_id = people.id
INNER JOIN movies ON movies.id = stars.movie_id
WHERE movies.title IN (
SELECT DISTINCT movies.title
FROM movies
INNER JOIN stars ON stars.movie_id = movies.id
INNER JOIN people ON people.id = stars.person_id
WHERE people.name = "Kevin Bacon" AND people.birth = "1958");

推荐答案

尝试

SELECT name 
FROM   people 
WHERE  id IN (SELECT DISTINCT person_id 
              FROM   stars 
              WHERE  movie_id IN (SELECT movie_id 
                                  FROM   stars 
                                         INNER JOIN people 
                                                 ON stars.person_id = people.id 
                                  WHERE  people.name = "kevin bacon" 
                                         AND people.birth = 1958)) 
       AND NOT ( name = "kevin bacon" 
                 AND birth = 1958 ); 

说明-

• 首先 SELECT 所有 movie_id ，其中"kevin bacon"(出生于1958年的那个人)已出演
• 现在 SELECT 个已经在上述电影中出演过的所有 person_id s
• 现在 SELECT 所有这些人的名字
最后，仅排除凯文培根"(kevin bacon).(出生于1958年的那个人)

• First SELECT all movie_ids where "kevin bacon" (the one who was born in 1958) has starred
• Now SELECT all person_ids who've starred in the aforementioned movies
• Now SELECT the names of all these people
• Finally, exclude only "kevin bacon" (the one who was born in 1958) from this result

这篇关于cs50 pset7 13.sql-从结果中删除kevin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！