PostgreSQL 13：如何筛选拥有全部指定配件的房产记录

要实现必须拥有所有传入参数指定配件的房产查询，你原来用IN的方式只会返回包含任一指定配件的记录，无法满足“全部拥有”的要求。以下是几种适用于PostgreSQL 13的解决方案：

方法一：分组计数筛选（最直观通用） #

通过分组统计每个房产匹配指定配件的数量，只有数量等于指定配件总数的房产才符合条件：

仅获取房产ID #

SELECT prop_id
FROM properties2accessories
WHERE acc_id IN ('GAR', 'REN') -- 替换为你的目标配件ID
GROUP BY prop_id
HAVING COUNT(DISTINCT acc_id) = 2; -- 数字对应指定配件的总个数

关联房产表获取完整信息 #

如果需要同时获取房产名称，关联properties表即可：

SELECT p.prop_id, p.prop_name
FROM properties2accessories pa
JOIN properties p ON pa.prop_id = p.prop_id
WHERE pa.acc_id IN ('GAR', 'REN')
GROUP BY p.prop_id, p.prop_name
HAVING COUNT(DISTINCT pa.acc_id) = 2;

方法二：PostgreSQL数组包含运算符 #

利用PostgreSQL的数组特性，将每个房产的配件ID聚合为数组，再判断目标配件数组是否是该数组的子集：

SELECT p.prop_id, p.prop_name
FROM properties2accessories pa
JOIN properties p ON pa.prop_id = p.prop_id
GROUP BY p.prop_id, p.prop_name
HAVING ARRAY['GAR', 'REN'] <@ ARRAY_AGG(pa.acc_id);

ARRAY_AGG(pa.acc_id)会把每个房产的所有配件ID聚合成一个数组，<@运算符用于判断左侧的目标数组是否完全包含在右侧的房产配件数组中，满足条件的房产就是拥有所有指定配件的。

验证示例 #

• 当查询“Garden（GAR）”和“Recently renovated（REN）”时，两种方法都会返回prop_id 1、2；
• 当查询“Garden（GAR）”和“Terrace（TER）”时，替换参数后会返回prop_id 2、3，完全符合你的需求。

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