如何查询满足指定课程全部资质要求的可用培训师

需求说明 #

给定CourseID，列出所有具备该课程全部QID资质的培训师。此前尝试多种SQL连接方式仅能得到资质任意匹配的结果，无法实现全部匹配过滤。

示例数据表 #

TrainerQuals（培训师资质表） #

CourseRequiredQuals（课程要求资质表） #

预期输出 #

• 当@courseid = 1时，输出TrainerID：1、3；
• 当@courseid = 2时，输出TrainerID：2、3；
• 当@courseid = 3时，输出TrainerID：3。

解决方案 #

要实现全部资质匹配，核心是验证培训师的资质完全覆盖课程要求的所有资质，以下是两种高效实现方式：

方式一：分组统计匹配数量 #

通过关联培训师资质与课程要求资质，分组后对比培训师匹配的资质数量和课程所需资质的总数量，相等则说明完全覆盖：

DECLARE @courseid INT = 3; -- 替换为目标CourseID

SELECT tq.TrainerID
FROM TrainerQuals tq
JOIN CourseRequiredQuals crq 
    ON tq.QID = crq.QID
WHERE crq.CourseID = @courseid
GROUP BY tq.TrainerID
HAVING COUNT(DISTINCT tq.QID) = (
    SELECT COUNT(DISTINCT QID) 
    FROM CourseRequiredQuals 
    WHERE CourseID = @courseid
);

方式二：排除法筛选 #

利用双重NOT EXISTS，排除那些缺少至少一项课程要求资质的培训师，剩余的即为符合要求的：

DECLARE @courseid INT = 3; -- 替换为目标CourseID

SELECT DISTINCT tq.TrainerID
FROM TrainerQuals tq
WHERE NOT EXISTS (
    SELECT 1
    FROM CourseRequiredQuals crq
    WHERE crq.CourseID = @courseid
    AND NOT EXISTS (
        SELECT 1
        FROM TrainerQuals tq_inner
        WHERE tq_inner.TrainerID = tq.TrainerID
        AND tq_inner.QID = crq.QID
    )
);

补充说明 #

• 两种方式都能处理课程存在重复QID的情况（通过DISTINCT去重）；
• 方式一适合对性能要求较高的场景，方式二逻辑更直观，便于理解。

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