问题描述
所以我有3个表,actor(id,name),movie(id,name,year)和casts(aid,mid)(这是actor id和movie id)。我的目标是选择所有演员在1900年之前的电影,并在2000年后的电影。
我的查询是
so I have 3 tables, actor(id, name), movie (id,name,year) and casts(aid, mid) (which are actor id and movie id). My goal is to select all the actors who acted in a film before 1900 and also in a film after 2000.My query is
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;
这个查询花了很长时间,似乎没有产生正确的结果。
所以有人可以请帮助我吗?
this query took really long time and didnt seem to produce the right result.So someone could please help me?
推荐答案
我假设问题是表达式 a .id = c1.aid = c2.aid
。如果我没有错误,这首先比较 c1.aid
与 c2.aid
,然后与布尔结果 a.id
。
I assume the problem is the expression a.id = c1.aid = c2.aid
. If I am not mistaken, this first compares c1.aid
with c2.aid
and then the boolean result with 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和a.id = c2.aid
Or, if you better like the where
syntax of inner joins, just change a.id = c1.aid = c2.aid
to a.id = c1.aid and a.id = c2.aid
这篇关于sql列出在1900年之前在电影中演出的演员,以及2000年后的电影的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!