有人可以帮助我如何解决我的 SQL 吗？我正在处理五个表。
预期结果是，
"编写一个 SQL 查询，列出约翰尼 · 德普和海伦娜 · 邦汉姆 · 卡特主演的所有电影的标题"
您的查询应该为每部电影的标题输出一个单列的表。
您可以假设数据库中只有一个人的名字是 Johnny Depp。
您可以假设数据库中只有一个人的名字是 Helena Bonham 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”将是有帮助的，我确实在使用它。
4
我首先使用了一些 join 语句来获得一张表，其中有这两个演员主演的所有电影标题。
我通过按电影标题分组来清理数据，将类似的电影标题分组。
然后，我使用 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
HING COUNT(movies.title) = 2;
2
我像一样使用，它比使用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
1
使用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";
1
使用计数:
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);
本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处
评论列表（12条）