将一组分组值与其自身进行比较

Question

假设我有一个简单的表格，其中包含了关于人们航班的数据(乘客的ID和他乘坐的公司的ID)：

+-------------+-----------+
| PassengerID | CompanyID |
+-------------+-----------+
|          14 |         1 |
|          14 |         3 |
|          14 |         4 |
|          20 |         1 |
|          20 |         2 |
|          20 |         5 |
|          20 |         5 |
|          36 |         5 |
|          36 |         5 |
|          36 |         5 |
|          41 |         1 |
|          41 |         1 |
|          41 |         2 |
|          41 |         2 |
+-------------+-----------+

我想达到的目标是，让所有与超过一家公司一起飞行的乘客，以及每一家公司都有相同数量的航班。

我希望得到这样的结果：

+-------------+
| PassengerID |
+-------------+
|          14 |
|          41 |
+-------------+

解释：

良好身份证明：

14 -与3家公司一起飞行，各飞1班

41 -与2家公司一起飞行，各飞2班

身份证不好：

20 --与3家公司一起飞行，但2次与一家公司飞行，1次与其他公司飞行

36 -只和一个连一起飞

我试着考虑这个问题，就像我会按PassengerID和CompanyID分组一样，同时添加CompanyID计数，这样我就可以得到如下内容：

+-------------+-----------+------------------+
| PassengerID | CompanyID | COUNT(CompanyID) |
+-------------+-----------+------------------+
|          14 |         1 |                1 |
|          14 |         3 |                1 |
|          14 |         4 |                1 |
|          20 |         1 |                1 |
|          20 |         2 |                1 |
|          20 |         5 |                2 |
|          36 |         5 |                3 |
|          41 |         1 |                2 |
|          41 |         2 |                2 |
+-------------+-----------+------------------+

然后，我想使用有计数( PassengerID )>1来去除ID 36，但为此，我必须再次按PassengerID分组。

然后，我会使用一个自连接来比较同一乘客中的计数，但是我在PassengerID 20上遇到了问题，因为自动连接将加入这些行：

+-------------+-----------+------------------+
| PassengerID | CompanyID | COUNT(CompanyID) |
+-------------+-----------+------------------+
|          20 |         1 |                1 |
|          20 |         2 |                1 |
+-------------+-----------+------------------+

并将忽略使用相同PassengerID的错误行：

+-------------+-----------+------------------+
| PassengerID | CompanyID | COUNT(CompanyID) |
+-------------+-----------+------------------+
|          20 |         5 |                2 |
+-------------+-----------+------------------+

我没有尝试执行这个查询，只是考虑了可能的解决方案，所以它可能不会这样工作。

Answer 1

您可以进行两个级别的聚合：

select PassengerID
from (select PassengerID, CompanyID, count(*) as numflights
      from flights f
      group by PassengerID, CompanyID
     ) f
group by PassengerID
having count(*) > 1 and                     -- more than one flight
       min(numflights) = max(numflights);   -- all the same number

Answer 2

我与公司计数一起选择PassengerID，然后在外部选择中选择与标准相对应的组。

select PassengerID
from (
select PassengerID, count(*) as c
from yourtable
group by PassengerID, CompanyID) t
group by PassengerID
having min(c) = max(c) and count(*) > 1