SQL列出了在1900年之前出演电影和2000年之后出演电影的演员

Question

所以我有3个表，演员（id，名字），电影（id，名字，年份）和演员（aid，mid）（这是演员ID和电影ID）。我的目标是选择所有在1900年之前出演电影和2000年之后出演电影的演员。我的查询是

select a.id 
from actor a, movie m1, casts c1, movie m2, casts c2
where a.id = c1.aid = c2.aid and c1.mid = m1.id and c2.mid = m2.id and
m1.year >2000 and m2.year <1900;

此查询花费了很长时间，似乎没有产生正确的结果。所以有人可以帮我吗？

Answer 1

若要获取在两个日期范围内出演电影的演员，请使用两个子查询。 像这样：

select yourFields
from yourTables
where actorId in (subquery to get actor id's for one date range)
and actorId in (subquery to get actor id's for second  date range)

您可以制定细节。

Answer 2

我认为问题是表达式a.id = c1.aid = c2.aid。如果我没记错的话，这首先将c1.aid与c2.aid进行比较，然后将布尔结果与a.id进行比较。

你可以试试这个：

select a.id 
from actor a
inner join casts c1 on c1.aid = a.id
inner join casts c2 on c2.aid = a.id
inner join movie m1 on c1.mid = m1.id
inner join movie m2 on c2.mid = m2.id 
where m1.year >2000 and m2.year <1900; 

或者，如果您更喜欢内部联接的where语法，只需将a.id = c1.aid = c2.aid更改为a.id = c1.aid and a.id = c2.aid

Answer 3

这也行得通

 
with first as (
SELECT Trim(mc.pid) as pid1
FROM   m_cast mc 
WHERE  Trim(mc.mid) IN (SELECT Trim(m.mid) 
                        FROM   movie m 
                        WHERE  Cast(Substr(Trim(m.year), -4) AS INTEGER) < 1970)
),
second as (
SELECT Trim(mc.pid) as pid2
FROM   m_cast mc 
WHERE  Trim(mc.mid) IN (SELECT Trim(m.mid) 
                        FROM   movie m 
                        WHERE  Cast(Substr(Trim(m.year), -4) AS INTEGER) > 1990) 
)
select first.pid1 from first intersect select second.pid2 from second

Answer 4

由于表的大小和大量的联接，此查询可能需要很长时间才能运行。

此查询返回结果，因为数据库中存在错误。

正确的查询是：

SELECT DISTINCT a1.fname, a2.lname 
FROM 
-- Create the table used to get all movies before 1900
actor AS a1
INNER JOIN casts AS c1
ON a1.id=c1.pid
INNER JOIN movie as m1
on m1.id = c1.mid,
-- Create the table used to get all movies after 2000
actor as a2
INNER JOIN casts AS c2
ON a2.id=c2.pid
INNER JOIN movie as m2
on m2.id = c2.mid
-- Only display actors that have played before 1900 and after 2000
WHERE m1.year < 1900 AND m2.year > 2000 AND a1.id = a2.id;