如何通过SQL JOIN实现带OR/AND条件的岗位资质匹配查询

需求拆解 #

每个岗位的资质要求分为两类：

• 必选资质：QUALIFICATION_REQ = 999类，员工必须全部拥有
• 可选资质：QUALIFICATION_REQ < 100类，员工至少拥有其中一个

核心实现思路 #

通过关联两张表，结合分组聚合与条件判断，分别校验必选、可选资质的满足情况：

SELECT 
    w.INTRAWORKNO AS 岗位编号,
    p.PERSV AS 员工编号
FROM works w
JOIN PERS_ALLOWED p 
    ON w.QUALIFICATION_REQ = p.ALLOWED
GROUP BY w.INTRAWORKNO, p.PERSV
HAVING 
    -- 校验必选资质：有999类资质则必须全部满足，无则自动通过
    (SELECT COUNT(*) FROM works WHERE INTRAWORKNO = w.INTRAWORKNO AND QUALIFICATION_REQ = 999) = 0
    OR
    SUM(CASE WHEN w.QUALIFICATION_REQ = 999 THEN 1 ELSE 0 END) = 
        (SELECT COUNT(*) FROM works WHERE INTRAWORKNO = w.INTRAWORKNO AND QUALIFICATION_REQ = 999)
    AND
    -- 校验可选资质：有小于100的资质则至少满足一个，无则自动通过
    (SELECT COUNT(*) FROM works WHERE INTRAWORKNO = w.INTRAWORKNO AND QUALIFICATION_REQ < 100) = 0
    OR
    SUM(CASE WHEN w.QUALIFICATION_REQ < 100 THEN 1 ELSE 0 END) >= 1;

代码逻辑说明 #

1. 关联表：通过QUALIFICATION_REQ与ALLOWED匹配，得到员工-岗位-对应资质的关联记录
2. 分组聚合：按岗位和员工分组，统计该员工对当前岗位各类资质的满足数量
3. 必选资质校验：对比当前岗位999类资质的总数，与员工拥有的该类资质数量，相等则说明全部满足
4. 可选资质校验：统计员工拥有的当前岗位小于100的资质数量，≥1则说明满足至少一个可选要求
5. 边界处理：针对岗位无某类资质的情况，通过子查询判断后自动放行，避免筛选异常

内容的提问来源于stack exchange，提问作者Lukas Fürst