sql - SQL查询以获取包含所有B列的A列

Question

我有一张有 2 列 customer 和 city 的表。有 4 个独特的城市 (w,x,y,z) 我想要与所有城市相关联的客户。所以对于表

+----------+------+
| 客户 | 城市 |
+----------+------+
| 一个 | W |
| 一个 | X |
| 一个 | 是 |
| 一个 | Z |
| 乙| X |
| C | 是 |
| Z | X |
| Z | 是 |
+----------+------+

所需的输出是

+----------+------+
| 客户 | 城市 |
+----------+------+
| 一个 | W |
| 一个 | X |
| 一个 | 是 |
| 一个 | Z |
+----------+------+

我使用的查询是：

Select Customer,City
From Table As T
Where Exists    (
                Select 1
                From Table As T2
                Where T2.Customer = T.City
                    And T2.City<> T.City
                )

标签： sqldatabaseamazon-web-servicesamazon-athena

Answer 1

通过聚合和 HAVING 子句中的条件，您可以获得所需的客户：

select customer
from tablename
group by customer
having count(*) = (select count(distinct city) from tablename)

您可以将它与运算符 IN 一起使用：

select * from tablename
where customer in (
  select customer
  from tablename
  group by customer
  having count(*) = (select count(distinct city) from tablename)
)