使用复杂子查询在SQL中练习
我正在尝试在SQL中执行查询。数据库是IMBD之一。这是形式:使用复杂子查询在SQL中练习
所以,我需要从电影低俗小说(ID = 2175869),演员/女演员,从来没有与低俗小说的另一位演员/女演员没有其他影片共同担任。
它应该是这样的:
SELECT person_id FROM cast_info WHERE movie_id = 2175869 AND
person_id NOT IN (SELECT)
但我无法弄清楚如何做的第二部分。不是低俗小说等
查询后用作子查询,返回电影哪里低俗小说不止一个演员在其中扮演
SELECT movie_id
FROM cast_info
WHERE
movie_id <> 2175869 AND
person_id IN (SELECT person_id FROM cast_info WHERE movie_id = 2175869)
GROUP BY movie_id
HAVING COUNT(*) > 1
现在得到低俗小说玩的人谁没有这样的电影玩
SELECT person_id
FROM cast_info
WHERE
movie_id = 2175869 AND
person_id NOT IN (
SELECT person_id FROM cast_info WHERE movie_id IN (
SELECT movie_id
FROM cast_info
WHERE
movie_id <> 2175869 AND
person_id IN (SELECT person_id FROM cast_info WHERE movie_id = 2175869)
GROUP BY movie_id
HAVING COUNT(*) > 1
)
)
注:我假设一个演员只能在每部电影中铸造一次。如果同一个演员可以在同一部电影中担任不同的角色,请用COUNT(DISTINCT person_id)
替换COUNT(*)
。
检查,这是一个非常大的数据库,所以它需要时间,但它运行正确知道,我会迟早发布结果。谢谢! –
首先,你需要看到低俗小说所有的演员:
SELECT person_id as pulp_actor_id
FROM cast_info
WHERE movie_id = 2175869
然后,你需要找到所有的演员二重奏:
SELECT c1.person_id as actor1_id
c2.person_id as actor2_id
FROM cast_info c1
JOIN cast_info c2
ON c1.person_id < c2.person_id
WHERE c1.movie_id = 2175869
AND c2.movie_id = 2175869;
找到所有的电影,其中二人一起行动低俗小说旁。
SELECT actor1_id, actor2_id, actor1_movies.movie_id
FROM (SELECT c1.person_id as actor1_id
c2.person_id as actor2_id
FROM cast_info c1
JOIN cast_info c2
ON c1.person_id < c2.person_id
WHERE c1.movie_id = 2175869
AND c2.movie_id = 2175869) as duos
JOIN cast_info as actor1_movies
ON duos.actor1_id = actor1_movies.person_id
AND actor1.movie_id <> 2175869
LEFT JOIN cast_info actor2_movies
ON duos.actor2_id = actor2_movies.person_id
AND actor1_movies.movie_id = actor2_movies.movie_id
WHERE actor2.movie_id IS NOT NULL;
现在得到所有从低俗小说演员不是最后的结果
SELECT person_id as pulp_actor_id
FROM cast_info c
LEFT JOIN (SELECT actor1_id, actor2_id, actor1_movies.movie_id
FROM (SELECT c1.person_id as actor1_id
c2.person_id as actor2_id
FROM cast_info c1
JOIN cast_info c2
ON c1.person_id < c2.person_id
WHERE c1.movie_id = 2175869
AND c2.movie_id = 2175869) as duos
JOIN cast_info as actor1_movies
ON duos.actor1_id = actor1_movies.person_id
AND actor1.movie_id <> 2175869
LEFT JOIN cast_info actor2_movies
ON duos.actor2_id = actor2_movies.person_id
AND actor1_movies.movie_id = actor2_movies.movie_id
WHERE actor2.movie_id IS NOT NULL;
WHERE movie_id = 2175869) as duos
ON c.person_id IN (actor1_id, actor2_id)
WHERE duos.movie_id IS NULL
谢谢,我会试试它,但这需要时间,我会向你发布结果。 –
Hola Jaime,Check Olver的答案,比我的简单。 –
Muy honesto por tu parte Juan Carlos,gracias,voy a ello –
@Sami我不是很肯定它。你是指@Harshil一样吗? –
是的,他指的是和我一样的问题 –
Okey,谢谢,我现在正在学习 –