SQL查询以查找具有至少一个指定值的行 [英] SQL query to find rows with at least one of the specified values

问题描述

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

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查询以查找具有至少一个指定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！