sqlite cs50 pset7电影sql.12

acruukt9 于 2022-11-24 发布在 SQLite

关注(0)|答案(6)|浏览(174)

有人能帮助我如何修复我的SQL吗？我正在处理五个表。
预期的结果是，
1.“编写一个SQL查询以列出约翰尼·德普和海伦娜·博纳姆·卡特主演的所有电影的片名”
1.您的查询应该会输出一个数据表，其中每部电影的标题只有一个数据行。
1.您可以假设数据库中只有一个人的名字是Johnny Depp。
1.您可以假设数据库中只有一个人的名字是Helena博纳姆Carter。

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)
             );

这将产生59行，而本应只有6行。

SELECT title FROM movies  WHERE id IN (SELECT DISTINCT movie_id FROM
 stars WHERE person_id =  (SELECT id FROM people  WHERE name IN
 ("Johnny Depp", "Helena Bonham Carter")));

我看到其他帖子谈论使用“WHERE IN”将是有帮助的，我确实在使用它。

sqlite

来源：https://stackoverflow.com/questions/61536261/cs50-pset7-movies-sql-12

6条答案

按热度按时间

elcex8rz1#

我首先使用几个连接语句得到一个表，其中有这两个演员主演的所有电影的片名。
我通过按电影标题分组来清理数据，这将类似的电影标题分组。
然后我使用COUNT函数查看电影标题在哪里被提到两次，这将意味着两个演员都参与其中。

-- inspired by https://stackoverflow.com/a/477035

SELECT movies.title FROM stars
JOIN movies ON stars.movie_id = movies.id
JOIN people ON stars.person_id = people.id
WHERE people.name IN ('Johnny Depp', 'Helena Bonham Carter')
GROUP BY movies.title
HAVING COUNT(movies.title) = 2;

赞(0）回复(0）举报 2022-11-24

piok6c0g2#

我使用了下面的like，它比使用INTERSECT快一点。

SELECT title FROM movies WHERE id IN (
SELECT movie_id FROM stars
JOIN people
ON stars.person_id = people.id
WHERE name = "Helena Bonham Carter" AND movie_id IN
(SELECT movie_id FROM stars WHERE person_id IN 
(SELECT id FROM people WHERE name = "Johnny Depp")
))
ORDER BY title

赞(0）回复(0）举报 2022-11-24

zaq34kh63#

使用JOIN和INTERSECT帮助我解决了这个问题。
大概是这样的：

Select title from movies 
join stars on stars.movie_id = movies.id 
join people on people.id = stars.person_id 
where people.name = "Helena Bonham Carter" 
INTERSECT 
Select title from movies 
join stars on stars.movie_id = movies.id 
join people on people.id = stars.person_id 
where people.name = "Johnny Depp";

赞(0）回复(0）举报 2022-11-24

qojgxg4l4#

使用计数：

SELECT title FROM movies WHERE id IN 
(SELECT movie_id FROM 
(SELECT count(person_id) as together, movie_id FROM stars WHERE person_id IN
(SELECT id FROM people WHERE name IN ('Johnny Depp','Helena Bonham Carter')) 
GROUP BY movie_id) 
WHERE together > 1);

赞(0）回复(0）举报 2022-11-24

vlf7wbxs5#

我已经测试了这里的所有建议，我相信这是迄今为止最快的一个，虽然它似乎有点神秘：
时间：0.48

SELECT title FROM movies WHERE id IN 
(
    SELECT movie_id FROM stars WHERE person_id =
        (SELECT id FROM people WHERE name = "Helena Bonham Carter") 
    and
        movie_id IN
            (
                 SELECT movie_id FROM stars WHERE person_id =
                     (SELECT id FROM people WHERE name = "Johnny Depp")
            )
)

赞(0）回复(0）举报 2022-11-24

jrcvhitl6#

在这种情况下，我没有使用JOIN选项，而更多的是作为传统的风格，以使它更好地在我的脑海中。我仍然会尝试另一种方式。但这个选项工作得很好。

SELECT movies.title FROM movies
WHERE id IN (SELECT movie_id FROM stars WHERE person_id IN
(SELECT id FROM people WHERE name = "Johnny Depp"))
AND id IN (SELECT movie_id FROM stars WHERE person_id IN
(SELECT id FROM people WHERE name = "Helena Bonham Carter"));

赞(0）回复(0）举报 2022-11-24

我来回答

sqlite cs50 pset7电影sql.12

6条答案

相关问题

热门标签

最新问答