SQL查询以查找具有至少一个指定值的行

本文介绍了SQL查询以查找具有至少一个指定值的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设您有两个表.一种叫做电影:

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') )