oracle 用于查找至少具有指定值之一的行的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7533933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to find rows with at least one of the specified values
提问by Mike Christensen
Suppose you had two tables. One called MOVIES:
假设你有两张桌子。一种叫做电影：
- MovieId
- MovieName
- 电影编号
- 电影名称
Then another called ACTORS that contains people who appear in those movies:
然后是另一个名为 ACTORS 的人，其中包含出现在这些电影中的人：
- 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".
现在，我想编写一个查询，返回包含一个或多个以下演员的任何电影：“汤姆汉克斯”、“罗素克劳”或“阿诺德施瓦辛格”。
One way to do it would be something like:
一种方法是：
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');
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.
这很好，但是在我的情况下，我可能在 WHERE 子句上有更多这样的条件，所以我想找到一种方法让 MOVIES 表成为我选择的主表。
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.
查询此内容的最佳方法是什么？如果这很重要，我正在使用 Oracle 11g，但我希望使用标准的 SQL 方法。
回答by Bogdan Sahlean
You can use EXISTSor INsubqueries:
您可以使用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')
)
or
或者
SELECT *
FROM MOVIES m
WHERE m.MovieId IN
(
SELECT a.MovieId
FROM ACTORS a
WHERE a.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger')
)
回答by Erwin Brandstetter
First you should have a 3rd table implementing the n:m relationship:
首先你应该有一个实现 n:m 关系的第三个表：
CREATE TABLE movie (
movie_id int primary key
,moviename text
-- more fields
);
CREATE TABLE actor (
actor_id int primary key
,actorname text
-- more fields
);
CREATE TABLE movieactor (
movie_id int references movie(movie_id)
,actor_id int references actor(actor_id)
,CONSTRAINT movieactor_pkey PRIMARY KEY (movie_id, actor_id)
);
Then you select like this:
然后你这样选择：
SELECT DISTINCT m.movie_id, m.moviename
FROM movie m
JOIN movieactor ma USING (movie_id)
JOIN actor a USING (actor_id)
WHERE a.actorname IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger');
Note, that text literals are enclose in single quotes!
请注意，文本文字用单引号括起来！
