如何实现SQL精确匹配IN子句中所有目标肌肉键的查询？

你的原查询存在两个核心问题：

1. WHERE target_muscles.muscle_key IN (a,b) 会过滤掉训练1中肌肉为c的行，导致分组后仅统计a和b的数量，无法识别该训练还有额外肌肉。
2. 未校验训练的总肌肉数量，也没有排除包含指定集合外肌肉的训练。

以下是几种可行的解决方案：

方法一：分组后双重校验 #

直接在HAVING子句中同时验证两个条件：训练的总肌肉数等于指定集合的大小，且没有任何肌肉不在指定集合内。

SELECT w.id
FROM workouts w
JOIN target_muscles tm ON w.id = tm.workout_id
GROUP BY w.id
HAVING COUNT(DISTINCT tm.muscle_key) = 2 -- 总肌肉数量必须等于指定集合的元素数
   AND SUM(CASE WHEN tm.muscle_key NOT IN ('a','b') THEN 1 ELSE 0 END) = 0 -- 不存在额外肌肉

方法二：子查询排除法 #

先筛选出总肌肉数符合要求的训练，再排除包含额外肌肉的训练：

SELECT id
FROM workouts
WHERE id IN (
    -- 找出总肌肉数为2的训练
    SELECT workout_id
    FROM target_muscles
    GROUP BY workout_id
    HAVING COUNT(DISTINCT muscle_key) = 2
)
AND id NOT IN (
    -- 排除包含非a/b肌肉的训练
    SELECT workout_id
    FROM target_muscles
    WHERE muscle_key NOT IN ('a','b')
)

方法三：集合运算（适用于支持INTERSECT/EXCEPT的数据库） #

通过集合的交集和差集操作，精准筛选符合条件的训练：

-- 先找到同时包含a和b的训练
SELECT workout_id FROM target_muscles WHERE muscle_key = 'a'
INTERSECT
SELECT workout_id FROM target_muscles WHERE muscle_key = 'b'
-- 再去掉包含额外肌肉的训练
EXCEPT
SELECT workout_id FROM target_muscles WHERE muscle_key NOT IN ('a','b')

注意事项 #

如果muscle_key是字符串类型，务必给值加上单引号（如'a'），否则数据库会将a、b识别为列名，导致错误。

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