如何基于元组条件筛选同时满足多技能要求的员工数据

现有员工技能数据表 #

需求 #

需要获取同时具备所有指定技能-等级条件的员工，指定条件为：

• 技能ID 560，等级 BEGINNER
• 技能ID 780，等级 INTERMEDIATE

预期结果如下：

补充说明：员工2354不应被返回，尽管他有技能780的INTERMEDIATE等级，但技能560的等级是INTERMEDIATE，未满足BEGINNER的要求。

尝试的SQL（不符合需求） #

使用OR会返回满足任一条件的记录，不符合“同时满足所有条件”的要求：

select * from employees_skills mec where (mec.skill_id, mec.skill_level) = (560, 'BEGINNER') or (mec.skill_id, mec.skill_level) = (780, 'INTERMEDIATE')

解决方案 #

要筛选出同时满足所有技能-等级条件的员工，有几种高效的实现方式：

方法1：分组统计筛选 #

先过滤出符合指定条件的记录，按员工ID分组后，确保分组内匹配的技能-等级组合数等于指定条件的数量（此处为2），最后关联回原表获取对应记录：

SELECT es.*
FROM employees_skills es
JOIN (
    SELECT employe_id
    FROM employees_skills
    WHERE (skill_id, skill_level) IN ((560, 'BEGINNER'), (780, 'INTERMEDIATE'))
    GROUP BY employe_id
    HAVING COUNT(DISTINCT skill_id, skill_level) = 2
) qualified ON es.employe_id = qualified.employe_id
WHERE (es.skill_id, es.skill_level) IN ((560, 'BEGINNER'), (780, 'INTERMEDIATE'));

方法2：多表内连接 #

通过多次内连接，强制员工同时满足所有指定的技能-等级条件：

SELECT es.*
FROM employees_skills es
WHERE es.employe_id IN (
    SELECT es1.employe_id
    FROM employees_skills es1
    JOIN employees_skills es2 
        ON es1.employe_id = es2.employe_id
    WHERE 
        es1.skill_id = 560 AND es1.skill_level = 'BEGINNER'
        AND es2.skill_id = 780 AND es2.skill_level = 'INTERMEDIATE'
)
AND (es.skill_id, es.skill_level) IN ((560, 'BEGINNER'), (780, 'INTERMEDIATE'));

方法3：窗口函数筛选（适用于支持窗口函数的数据库） #

用窗口函数统计每个员工匹配的技能-等级数量，再筛选出数量达标的记录：

WITH skill_matches AS (
    SELECT 
        *,
        COUNT(DISTINCT (skill_id, skill_level)) OVER (PARTITION BY employe_id) AS match_count
    FROM employees_skills
    WHERE (skill_id, skill_level) IN ((560, 'BEGINNER'), (780, 'INTERMEDIATE'))
)
SELECT employe_id, skill_level, skill_id
FROM skill_matches
WHERE match_count = 2;

以上方法均能确保仅返回同时满足所有指定技能-等级条件的员工记录，符合预期需求。

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