oracle SQL SELECT：我需要一个“全部”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14297354/
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 SELECT: I need a "IN ALL" Clause
提问by user1069968
I have two tables. And I need all organizer_ids that organized ALL events between 2010 and 2012.
我有两张桌子。我需要所有organizer_id在 2010 年到 2012 年之间组织过所有活动的人。
event
---------
event_id
name
year
event_organizer
---------
event_id
organizer_id
I tried something like this:
我试过这样的事情：
SELECT organizer_id
FROM event_organizer
WHERE event_id IN (SELECT event_id
FROM event
WHERE year>=2010 AND year<=2012);
But it didn't work because I need only the organizer_idwhich have organized ALL events between 2010 and 2012. I have no idea how the query should look like that it works as I want.
但它不起作用，因为我只需要organizer_id在 2010 年和 2012 年之间组织了所有事件的那些。我不知道查询应该如何像我想要的那样工作。
回答by ruakh
You can write:
你可以写：
SELECT t1.organizer_id
FROM ( SELECT eo.organizer_id,
COUNT(1) AS num_events
FROM event_organizer eo
JOIN event e
ON eo.event_id = e.event_id
WHERE e.year BETWEEN 2010 AND 2012
GROUP
BY eo.organizer_id
) t1
JOIN ( SELECT COUNT(1) AS num_events
FROM event e
WHERE e.year BETWEEN 2010 AND 2012
) t2
ON t1.num_events = t2.num_events
;
(The idea for the above is: for each organizer, find out how many events (s)he organized between 2010 and 2012. Then, compare this to the total number of events between 2010 and 2012. If these two numbers are the same, then the organizer organized all events in that time period.)
（上面的想法是：对于每个组织者，找出他在 2010 年和 2012 年之间组织了多少活动。然后，将其与 2010 年和 2012 年之间的活动总数进行比较。如果这两个数字相同，然后组织者组织了该时间段内的所有活动。）
Alternatively, you can write:
或者，您可以编写：
SELECT organizer_id
FROM organizer -- I assume you have an 'organizer' table?
WHERE organizer_id NOT IN
( SELECT o.organizer_id
FROM event e
CROSS
JOIN organizer o
LEFT
OUTER
JOIN event_organizer eo
ON e.event_id = eo.event_id
AND o.organizer_id = eo.organizer_id
WHERE e.year BETWEEN 2010 and 2012
AND eo.event_id IS NULL
)
;
(The idea for this one is: find all event–organizer pairings such that the organizer did notorganize the event. Then, return all organizers who don't appear in any such pairings.)
（这一个想法是：找到所有事件的组织者配对使得组织者并没有组织活动之后，回到谁不会出现在任何这样的配对所有的组织者。）
回答by Gordon Linoff
I think the simplest way to do such a query is with an aggregation:
我认为进行此类查询的最简单方法是使用聚合：
with events as (
select event_id
FROM event
WHERE year>=2010 AND year<=2012
)
select organizer_id
from event_organizer eo
where event_id in (select event_id from events)
group by organizer_id
having count(distinct event_id) = (select count(*) from events)
