关于在一对多结构数据表中精准匹配指定全部skillID且无多余skillID的SQL查询求助

我来帮你搞定这个从一对多结构中找出恰好包含指定skillID集合、无额外skill的记录id的问题！你之前创建临时表统计每个id的skill数量是完全正确的方向，这正是验证“没有多余skill”的关键一步，下面给你几种可行的SQL实现方案：

核心思路 #

要满足你的需求，需要同时验证两个核心条件：

• 目标id包含所有指定的skillID（一个都不能少）
• 目标id没有任何不在指定集合中的skillID（一个都不能多）

方案1：分组统计+条件过滤（最简洁） #

适合单组skill集合的查询，直接通过GROUP BY和HAVING子句同时验证两个条件：

-- 匹配第一组skill集合：1004464、1006543、1004605、1006740
SELECT id
FROM table1
GROUP BY id
HAVING 
  -- 条件1：确保包含所有4个指定skill（去重统计匹配到的数量等于目标集合大小）
  COUNT(DISTINCT CASE WHEN skillID IN (1004464, 1006543, 1004605, 1006740) THEN skillID END) = 4
  -- 条件2：确保没有额外skill（总skill数量等于目标集合大小）
  AND COUNT(DISTINCT skillID) = 4;

注意：如果你的数据中同一个id不会重复出现同一个skillID，可以去掉DISTINCT来提升查询性能。

方案2：子查询排除多余skill（逻辑更直观） #

先排除所有包含非目标skill的id，再筛选出包含全部目标skill的id：

SELECT id
FROM table1
-- 第一步：排除有多余skill的id
WHERE id NOT IN (
  SELECT DISTINCT id
  FROM table1
  WHERE skillID NOT IN (1004464, 1006543, 1004605, 1006740)
)
-- 第二步：确保包含所有目标skill
GROUP BY id
HAVING COUNT(DISTINCT skillID) = 4;

方案3：批量处理多组skill集合（可扩展） #

如果你需要同时处理多组skill集合，建议先创建一个存储各组skill的表，再通过关联查询一次性找出所有符合条件的id：

1. 创建skill组表 #

CREATE TABLE skill_groups (
  group_id INT, -- 组标识，比如第一组为1，第二组为2
  skillID INT
);

-- 插入各组skill数据
INSERT INTO skill_groups VALUES 
  (1, 1004464), (1, 1006543), (1, 1004605), (1, 1006740),
  (2, 1003914), (2, 1005354), (2, 1004701);

2. 查询所有符合各组条件的id #

SELECT sg.group_id, t.id
FROM table1 t
JOIN skill_groups sg ON t.skillID = sg.skillID
GROUP BY sg.group_id, t.id
HAVING 
  -- 条件1：当前id包含该组所有skill
  COUNT(DISTINCT t.skillID) = (SELECT COUNT(*) FROM skill_groups WHERE group_id = sg.group_id)
  -- 条件2：当前id没有该组以外的skill
  AND NOT EXISTS (
    SELECT 1
    FROM table1 t2
    WHERE t2.id = t.id
    AND t2.skillID NOT IN (SELECT skillID FROM skill_groups WHERE group_id = sg.group_id)
  );

这个方案可以轻松扩展到更多组，不需要重复修改查询语句。

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