sql - 仅使用具有 `in` 子句中所有值的列连接查询

Question

我正在为我的网站创建一个简单的过滤系统。我在 field 和便利设施之间存在多对多关系。这是我的表格。

注意:所有 id 都是 uuid。为了简单起见，将它们简称为

地点:

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |
| 'bbb' | 'second venue' |
| 'ccc' | 'third venue'  |

便利设施:

| id    |      name        |
___________________________
| 'aaa' |  'first amenity' |
| 'bbb' | 'second amenity' |
| 'ccc' | 'third amenity'  |

便利设施_ field :

| amenity_id  |    venue_id  |
______________________________
| 'aaa'       |  'aaa'       |
| 'bbb'       | 'aaa'        |
| 'ccc'       | 'aaa'        |
| 'aaa'       | 'bbb'        |
| 'bbb'       | 'ccc'        |

我正在尝试编写一个查询以返回至少具有所有传入的 amenity_id 的场所。例如传入 amenity_ids aaa 和 bbb。

当传入的便利设施 ID 为 aaa 和 bbb 时，我要查找的输出。

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |

最初我试过这个查询

select * from venues 
INNER JOIN amenity_venue ON amenity_venue.venue_id = venues.id
where amenity_id in ('aaa', 'bbb');

这将返回所有具有 amenity_id aaa 或 bbb

的场所

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |
| 'bbb' | 'second venue' |
| 'ccc' | 'third venue'  |

于是我天真地尝试了

select * from venues 
INNER JOIN amenity_venue ON amenity_venue.venue_id = venues.id
where amenity_id = 'aaa'
  and amenity_id = 'bbb';

什么都不返回。我正在尝试编写一个查询，其中如果 amenity_ids aaa 和 bbb 仅在会场 aaa 中传递，因为它是唯一具有与两种便利设施的关系。此外，便利设施的数量在查询之间是动态的。

Answer 1

您可以将 ID 聚合到一个数组中，然后将其与预期 ID 列表进行比较:

select v.*
from venues v
  join amenity_venue av ON av.venue_id = v.id
group by v.id
having array_agg(av.amenity_id) @> array['aaa', 'bbb'];

以上假定 venue.id 被声明为主键(因为 group by)。

如果您只想传递设施名称，您实际上不需要在查询中对 ID 进行硬编码:

select v.*
from venues v
  join amenity_venue av ON av.venue_id = v.id
group by v.id
having array_agg(av.amenity_id) @> array(select id 
                                         from amenities 
                                         where name in ('first amenity', 'second amenity'));

在线示例:https://rextester.com/FNNVXO34389