選擇匹配的行子集的更好方法？

DB Fiddle 連結。我有一個這樣的多對多表：

CREATE TABLE house_to_cats (
 id SERIAL PRIMARY KEY,
 house_id INTEGER,
 cat_id INTEGER
);

-- house with cats 1 and 2: too small
INSERT INTO house_to_cats (house_id, cat_id) VALUES (1, 1), (1, 2);
-- house with cats 1 2 3 4: too big
INSERT INTO house_to_cats (house_id, cat_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4);
-- house with cats 1 2 3: just right
INSERT INTO house_to_cats (house_id, cat_id) VALUES (3, 1), (3, 2), (3, 3);

我需要一個查詢，它接受任意的貓列表並返回匹配的房子（如果存在）。我想出了這個：

SELECT
   house_id
FROM (
   SELECT
         house_id
       , ARRAY_AGG(cat_id) as cat_id_agg
   FROM house_to_cats
   JOIN (
       SELECT DISTINCT
             house_id
       FROM house_to_cats
       JOIN (SELECT * FROM UNNEST(ARRAY[1, 2, 3]) cat_id) inn USING (cat_id)
   ) filter USING (house_id)
   GROUP BY house_id
) agg
WHERE cat_id_agg <@ ARRAY[1, 2, 3]
 AND cat_id_agg @> ARRAY[1, 2, 3];

有一個更好的方法嗎？

我的查詢背後的想法：在 中filter，獲取house_id其中至少有一隻我們的貓。在中，為所有這些agg創建數組。並在最外層的查詢中過濾掉與我們的集合不匹配的組。cat_id_agg``house_ids

如果我理解正確，您的查詢可以簡化為：

select house_id
from house_to_cats
group by house_id
having array_agg(cat_id order by cat_id) = array[1,2,3]

注意呼叫order by中的- 數組不等於數組。為避免由於聚合以不同順序完成而導致的不正確結果，聚合數組必須包含與比較值順序相同的值。array_agg()``[3,2,1]``[1,2,3]

線上範例：https ://rextester.com/IKGHWL59301

引用自：https://dba.stackexchange.com/questions/227194