如何识别两个独立实体的行是否存在

Question

我是SQL的新手。我有一个我不能得到结果的查询。

从下表中，我需要列出同时执行“阿波罗”和“航天飞机”任务的宇航员(阿斯诺号)(项目名称)。

projectname  | missionno | astrono |  role
--------------+-----------+---------+----------------------
 Apollo       | 1         |      22 | Commander
 Apollo       | 1         |      42 | Command Module Pilot
 Apollo       | 1         |      10 | Lunar Module Pilot
 Apollo       | 7         |      33 | Commander
 Apollo       | 7         |      16 | Command Module Pilot
 Apollo       | 7         |      14 | Lunar Module Pilot
 Apollo       | 8         |       5 | Commander
 Apollo       | 8         |      27 | Command Module Pilot
 Apollo       | 8         |       2 | Lunar Module Pilot
 Apollo       | 9         |      29 | Commander
 Apollo       | 9         |      36 | Command Module Pilot
 Apollo       | 9         |      35 | Lunar Module Pilot
 Apollo       | 10        |      39 | Commander
 Apollo       | 10        |      44 | Command Module Pilot
 Apollo       | 10        |       9 | Lunar Module Pilot
 Apollo       | 11        |       3 | Commander
 Apollo       | 11        |      11 | Command Module Pilot
 Apollo       | 11        |       1 | Lunar Module Pilot
 Apollo       | 12        |      12 | Commander
 Apollo       | 12        |      21 | Command Module Pilot
 Apollo       | 12        |       4 | Lunar Module Pilot
 Apollo       | 13        |      27 | Commander
 Apollo       | 13        |      40 | Command Module Pilot
 Apollo       | 13        |      23 | Lunar Module Pilot
 Apollo       | 14        |      37 | Commander
 Apollo       | 14        |      32 | Command Module Pilot
 Apollo       | 14        |      30 | Lunar Module Pilot
 Apollo       | 15        |      43 | Command Module Pilot
 Apollo       | 15        |      24 | Lunar Module Pilot
 Apollo       | 16        |      44 | Commander
 Apollo       | 16        |      28 | Command Module Pilot
 Apollo       | 16        |      15 | Lunar Module Pilot
 Apollo       | 17        |       9 | Commander
 Apollo       | 17        |      17 | Command Module Pilot
 Apollo       | 17        |      34 | Lunar Module Pilot
 Skylab       | 2         |      12 | Commander
 Skylab       | 2         |      41 | Pilot
 Skylab       | 2         |      25 | Scientist
 Skylab       | 3         |       4 | Commander
 Skylab       | 3         |      26 | Pilot
 Skylab       | 3         |      18 | Scientist
 Skylab       | 4         |       8 | Commander
 Skylab       | 4         |      31 | Pilot
 Skylab       | 4         |      19 | Scientist
 Apollo-Soyuz | 1         |      39 | Commander
 Apollo-Soyuz | 1         |       6 | Command Module Pilot
 Apollo-Soyuz | 1         |      38 | Docking Module Pilot
 Shuttle      | STS-1     |      44 | Commander
 Shuttle      | STS-1     |     119 | Pilot
 Shuttle      | STS-2     |     138 | Commander
 Shuttle      | STS-2     |     408 | Pilot
 Shuttle      | STS-3     |      25 | Commander
 Shuttle      | STS-3     |     156 | Pilot
 Shuttle      | STS-4     |      28 | Commander
 Shuttle      | STS-4     |     191 | Pilot
 Shuttle      | STS-5     |       6 | Commander
 Shuttle      | STS-5     |     309 | Pilot
 Shuttle      | STS-5     |      53 | Mission Specialist
 Shuttle      | STS-5     |     245 | Mission Specialist

任何帮助都是最好的。

谢谢。

Answer 1

一种选择是对每个宇航员的记录使用条件聚合：

SELECT astrono
FROM yourTable
GROUP BY astrono
HAVING SUM(CASE WHEN projectname = 'Apollo'  THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN projectname = 'Shuttle' THEN 1 ELSE 0 END) > 0

Answer 2

使用exists条件

Select astrono table t where exists (select 1 from table where astrono=t.astrono and project name=‘Apollo’)和exists (select 1 from table where astrono=t.astrono and projectname ='Shuttle')；

Answer 3

让我们一步一步来。在下面的查询中，我们只从原始表中获取与Apollo和Shuttle项目相关的所有行。

SELECT * FROM mytable WHERE projectname IN ('Apollo', 'Shuttle')

然后，因为我们不关心具体的项目，所以让我们修改上面的查询，以返回每个宇航员的项目：

SELECT astrono, count(*) AS count
FROM mytable
WHERE projectname IN ('Apollo', 'Shuttle')
GROUP BY astrono

最后，根据上面的统计，我们只能保留参与了至少两个项目的宇航员：

SELECT astrono
FROM mytable
WHERE projectname IN ('Apollo', 'Shuttle')
GROUP BY astrono
HAVING count(*)>=2;

由于我们已经剔除了引用其他项目的所有行，并且假设宇航员和项目的组合最多只能出现一次，那么如果根据上述计数发现一个宇航员参与了两个项目，则这两个项目必须是Apollo和Shuttle。

如果你不喜欢上面的假设，即宇航员和项目的每个组合最多只能出现一次，你也可以这样做：

SELECT astrono
FROM mytable
GROUP BY astrono
HAVING bool_or(projectname='Apollo') OR bool_or(projectname='Shuttle');