假设您有两个表.一种叫做电影:
Suppose you had two tables. One called MOVIES:
- MovieId
- 电影名称
另一个叫ACTORS的人,其中包含出现在那些电影中的人:
Then another called ACTORS that contains people who appear in those movies:
- MovieId
- ActorName
现在,我想编写一个查询,该查询返回包含以下演员之一或更多的任何电影:汤姆·汉克斯",罗素·克劳"或阿诺德·施瓦辛格".
Now, I want to write a query that returns any movie that contains ONE OR MORE of the following actors: "Tom Hanks", "Russell Crowe" or "Arnold Schwarzenegger".
一种实现方法是:
SELECT DISTINCT A.MovieId, M.MovieName FROM ACTORS A INNER JOIN MOVIES M USING (MovieId) WHERE A.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger');这完全可以,但是在我的情况下,我可能在WHERE子句中有多个这样的条件,所以我想找到一种方法使MOVIES表成为我从中选择的主表.
Which is perfectly fine, however in my case I might have several more of these conditions on the WHERE clause so I want to find a way to make the MOVIES table the primary table I select from.
查询此内容的最佳方法是什么?如果那很重要,我正在使用Oracle 11g,但我希望有一种标准的SQL方法.
What's the best way to query for this? I'm using Oracle 11g if that matters, but I'm hoping for a standard SQL method.
推荐答案您可以使用EXISTS或IN子查询:
SELECT * FROM MOVIES m WHERE EXISTS ( SELECT * FROM ACTORS a WHERE a.MovieId = m.MovieId AND a.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger') )或
SELECT * FROM MOVIES m WHERE m.MovieId IN ( SELECT a.MovieId FROM ACTORS a WHERE a.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger') )更多推荐
SQL查询以查找具有至少一个指定值的行
发布评论