我在此查询中有点挣扎。也许有人可以给我提示?
我的设置由3个表(演员,电影和关系)组成。我想做的是:
找到所有名字的演员,其名字始于与Persons一起在任何电影中播放的A播放。
这就是我走了多远:
SELECT Name
FROM Actors a, Movies m, Relations r
WHERE a.Name='A%'
AND Person1.keyPerson = m.KeyPerson
AND m.KeyMovies ....
关于如何继续的示例!
upadate-桌子看起来像这样:
演员(Actorsid,名称)电影(电影ID,标题)关系(Actorsid,Movieid)
有几种方法可以做到这一点 - 您可以做的一种方法是通过IN
语句,拉动电影Person1
已经进入并检查A%
人的电影。
Select Distinct A.Name
From Actors A
Join Relations R On R.ActorId = A.ActorId
Where A.Name Like 'A%'
And R.MovieId In
(
Select R2.MovieId
From Actors A2
Join Relations R2 On R2.ActorId = A2.ActorId
Where A2.Name = 'Person1'
)
另一种方法是通过EXISTS
语句,寻找Person1
所在A%
的任何电影也在:
Select A.Name
From Actors A
Join Relations R On R.ActorId = A.ActorId
Where A.Name Like 'A%'
And Exists
(
Select *
From Movies M2
Join Relations R2 On R2.MovieId = M2.MovieId
Join Actors A2 On R2.ActorId = R2.ActorId
Where A2.Name = 'Person1'
And M2.MovieId = R.MovieId
)
没有子查询的解决方案:
SELECT DISTINCT a.Name
FROM Actors a
INNER JOIN Relations r
ON r.actorsID = a.actorsID
INNER JOIN Relations r2
ON r.moviesID = r2.moviesID
INNER JOIN Actors a2
ON a2.actorsID = r2.actorsID
AND a2.Name = 'Person1'
WHERE a.Name LIKE 'A%'