PostgreSQL多对多查询：如何筛选仅关联指定分类的expenses记录

你可以通过对关联表按支出ID分组，搭配HAVING条件过滤的方式实现需求，两种常用写法如下：

写法1（推荐，兼容性好逻辑清晰） #

-- 仅查询符合条件的支出ID
SELECT expense_id
FROM expenses_categories
GROUP BY expense_id
HAVING 
  -- 确保同时包含两个目标分类
  COUNT(*) FILTER (WHERE category_id IN (1, 2)) = 2
  -- 确保总关联分类数正好为2，没有其他分类
  AND COUNT(*) = 2;

如果需要获取expenses表的完整支出记录，可以关联主表：

SELECT e.*
FROM expenses e
INNER JOIN (
  SELECT expense_id
  FROM expenses_categories
  GROUP BY expense_id
  HAVING COUNT(*) FILTER (WHERE category_id IN (1, 2)) = 2 AND COUNT(*) = 2
) ec ON e.id = ec.expense_id;

写法2（PostgreSQL数组语法，更简洁） #

利用PostgreSQL的数组聚合函数直接匹配分类集合：

SELECT expense_id
FROM expenses_categories
GROUP BY expense_id
-- 注意ARRAY_AGG内要加排序，避免分类顺序不同导致匹配失败
HAVING ARRAY_AGG(category_id ORDER BY category_id) = ARRAY[1,2];

逻辑说明 #

两种写法都满足两个核心过滤规则：

• 支出必须同时关联ID为1、2的两个分类
• 支出不能关联除1、2外的其他任何分类
  针对你给出的测试数据，两种写法都会返回expense_id = 1的结果，完全符合预期。

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