PostgreSQL中查询匹配指定goal_id组合的suggestion_id的实现方法咨询

你的问题出在原SQL只筛选了关联指定goal_id中任意一个的suggestion，并没有确保该suggestion关联的goal_id恰好是你指定的整个集合。不用重新设计表结构，调整SQL就能实现需求，下面提供两种适合PostgreSQL的方案：

方案1：利用GROUP BY + HAVING实现精确匹配 #

这种方式通过双重条件确保完全匹配：

1. 先筛选出关联了指定goal_id的记录
2. 确保该suggestion关联的不同goal_id数量等于你指定的数量
3. 确保该suggestion的所有关联记录都被包含在筛选结果中（没有额外的goal_id）

针对(12,14)的查询示例：

SELECT suggestion_id
FROM your_table -- 替换成你的实际表名
WHERE goal_id IN (12, 14)
GROUP BY suggestion_id
HAVING COUNT(DISTINCT goal_id) = 2
   AND COUNT(*) = (SELECT COUNT(*) FROM your_table WHERE suggestion_id = your_table.suggestion_id);

方案2：利用PostgreSQL数组特性（更简洁灵活） #

PostgreSQL原生支持数组操作，我们可以将每个suggestion关联的goal_id整理成排序后的数组，再和你指定的排序后数组对比——这样不管输入的goal_id顺序如何，只要元素和数量完全一致就能匹配。

针对(12,14)或(14,12)的查询：

SELECT suggestion_id
FROM your_table -- 替换成你的实际表名
GROUP BY suggestion_id
HAVING ARRAY_AGG(DISTINCT goal_id ORDER BY goal_id) = ARRAY[12,14]::INT[];

验证你的需求示例 #

• 查询(13,17)：

  SELECT suggestion_id
  FROM your_table
  GROUP BY suggestion_id
  HAVING ARRAY_AGG(DISTINCT goal_id ORDER BY goal_id) = ARRAY[13,17]::INT[];
  

  返回结果：2，符合预期。

• 查询(12,18)：

  SELECT suggestion_id
  FROM your_table
  GROUP BY suggestion_id
  HAVING ARRAY_AGG(DISTINCT goal_id ORDER BY goal_id) = ARRAY[12,18]::INT[];
  

  返回结果：4，符合预期。

关于表结构的说明 #

你当前的表结构是典型的多对多关联中间表，完全符合存储suggestion_id与goal_id关联关系的需求，不需要重新设计。

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