如何编写SQL查询：筛选匹配指定设施ID列表的餐馆

刚好碰到过类似的需求，这本质上是要找精确匹配所有指定设施、且没有额外设施的餐馆，给你两种实用的SQL写法：

方法一：通用GROUP BY + HAVING写法（适配MySQL、SQL Server、PostgreSQL等所有主流数据库） #

这种写法兼容性最好，不管用什么数据库都能跑：

SELECT r.id, r.name
FROM rests r
JOIN amenity_rest ar ON r.id = ar.rest_id
-- 替换括号里的内容为你传入的设施ID列表
WHERE ar.amenity_id IN (1, 3)
GROUP BY r.id, r.name
HAVING 
  -- 确保餐馆拥有传入的所有设施（计数等于列表长度）
  COUNT(DISTINCT ar.amenity_id) = 2
  -- 确保餐馆没有任何不在传入列表里的额外设施
  AND NOT EXISTS (
      SELECT 1
      FROM amenity_rest ar2
      WHERE ar2.rest_id = r.id
        AND ar2.amenity_id NOT IN (1, 3)
  );

逻辑解释： #

1. 先通过JOIN关联餐馆和它的设施记录，筛选出至少拥有一个指定设施的餐馆；
2. GROUP BY按餐馆分组后，COUNT(DISTINCT ar.amenity_id) = 列表长度确保该餐馆拥有所有指定设施（少一个的话计数就会小于列表长度）；
3. NOT EXISTS子句用来排除那些拥有指定设施之外其他设施的餐馆，保证完全匹配。

方法二：数组聚合写法（适合支持数组的数据库，如PostgreSQL） #

如果你的数据库支持数组操作，这种写法会更简洁直观：

SELECT r.id, r.name
FROM rests r
JOIN (
    -- 把每个餐馆的所有设施ID聚合为一个有序数组
    SELECT 
        rest_id, 
        ARRAY_AGG(DISTINCT amenity_id ORDER BY amenity_id) AS amenity_list
    FROM amenity_rest
    GROUP BY rest_id
) ar ON r.id = ar.rest_id
-- 直接对比数组是否完全相等（注意要和聚合时的排序一致）
WHERE ar.amenity_list = ARRAY[1, 3]::INT[];

逻辑解释： #

先通过子查询把每个餐馆的设施ID聚合为一个有序去重的数组，然后直接对比这个数组和你传入的设施ID数组是否完全一致，一步到位实现精确匹配。

验证你的测试案例： #

• 当传入设施ID列表[1,3]时：
  • 方法一会筛选出rest1（计数为2，且没有额外设施），返回正确结果；
  • 方法二中rest1的amenity_list是[1,3]，和传入数组匹配，返回正确结果。
• 当传入设施ID列表[1,2,3]时：
  • 没有任何餐馆同时拥有这三个设施，两种写法都会返回空结果，符合需求。

注意事项： #

• 如果amenity_rest表中存在同一个餐馆重复关联同一个设施的情况，一定要用DISTINCT（不管是COUNT(DISTINCT)还是ARRAY_AGG(DISTINCT)），避免计数或数组内容错误；
• 使用方法一时，要确保传入的设施ID列表没有重复值，否则COUNT的长度会和实际需要匹配的设施数量不符。

内容的提问来源于stack exchange，提问作者Shenouda Shehata