postgresql - 将总计分组匹配到总计的好方法是什么

Question

我有以下问题。看似很简单的一个查询，但我花了那么多时间也没弄明白。

Table-----projectmissioncraft

project can have many missions, for each mission, it can use multiple air crafts. I want to identify the crafts that was involved with all the missions.

For eg if there are 10 missions, mission 1 can use craft 1 and 2. If there is any craft that used for all the 10 missions (mission 1 -10), that should be output.

I wrote,

select craft,count(mission) from table group by craft

在此之后我不知道如何将这个值与任务总数相匹配。任何帮助将不胜感激。

Answer 1

如果你需要这个用于单个项目，你需要按任务总数过滤工艺任务数:

select c.id from (
   select c.id, count(mission_id) cnt
   from craft c inner join mission m on (m.id = c.mission_id)
   where m.project_id = <project_id> 
   group by c.id
) as c 
where c.cnt = (select count(*) from mission where project_id = <project_id>)

如果所有项目都需要这个:

select c.project_id, c.id from (
   select m.project_id, c.id, count(mission_id) cnt
   from craft c inner join mission m on (m.id = c.mission_id) 
   group by m.project_id, c.id
) as c inner join (
   select project_id, count(*) cnt
   from mission
   group by project_id
) as m on (m.project_id = c.project_id)
where c.cnt = m.cnt