SQL如何实现item关联option完全精确匹配的查询需求

实现该需求的核心逻辑是同时满足两个匹配条件：

• 目标item关联的所有option_id都完全包含在传入的选项数组中
• 目标item关联的去重后option_id的总数量，与传入选项数组的长度完全相等

通用兼容SQL实现 #

该写法适用于绝大多数关系型数据库（MySQL、PostgreSQL、SQL Server等）：

SELECT i.*
FROM items i
INNER JOIN (
    SELECT item_id
    FROM options
    GROUP BY item_id
    HAVING 
        -- 条件1：关联的option总数等于传入数组长度，这里3对应数组[1,2,3]的长度
        COUNT(DISTINCT option_id) = 3
        -- 条件2：没有不在传入数组内的option_id
        AND SUM(CASE WHEN option_id NOT IN (1,2,3) THEN 1 ELSE 0 END) = 0
) matched_items ON i.id = matched_items.item_id
WHERE i.ref = 'x2';

如果需要调整查询参数，直接修改三处对应值即可：

1. WHERE条件里的ref值
2. COUNT(DISTINCT option_id)的数值为传入数组的长度
3. NOT IN后的列表为传入的选项数组
   当没有匹配项时，查询结果返回空集，符合要求。

原写法错误说明 #

你之前的查询语句主要有三处问题：

• 子查询中重复关联了items表，会和外层的items表产生不必要的笛卡尔积，导致关联逻辑错误
• 分组条件错误，按options.id分组只能统计单条option记录的数量，无法得到单个item对应的所有option的统计值
• 缺少对「存在超出传入数组的option_id」场景的判断，会出现漏判

特定数据库简化写法 #

如果使用PostgreSQL，可通过数组聚合简化写法，逻辑更直观：

SELECT i.*
FROM items i
INNER JOIN (
    SELECT item_id, ARRAY_AGG(option_id ORDER BY option_id) AS option_arr
    FROM options
    GROUP BY item_id
) o ON i.id = o.item_id
WHERE i.ref = 'x2' AND o.option_arr = ARRAY[1,2,3]::INT[];

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