如何查询PERSONS状态为Active且OCCUPATION仅1条Terminated记录的结果

你原有SQL的问题是仅做了单条职业记录的状态过滤，没有校验员工全部职业记录的状态分布，因此会误筛出同时存在Active和Terminated状态职业记录的员工。

符合要求的SQL写法 #

写法1（窗口函数版本，逻辑清晰） #

SELECT 
    o.STATUS AS OCCUPATION_STATUS,
    p.STATUS AS PERSON_STATUS,
    p.UPID
FROM PERSONS p
INNER JOIN (
    SELECT 
        PERSONID,
        STATUS,
        -- 统计该员工总职业记录数
        COUNT(*) OVER (PARTITION BY PERSONID) AS total_occ_count,
        -- 统计该员工Active状态的职业记录数
        SUM(CASE WHEN STATUS = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY PERSONID) AS active_occ_count
    FROM OCCUPATION
) o ON p.QSID = o.PERSONID
WHERE 
    -- 人员本身状态为Active
    p.STATUS = 'Active'
    -- 职业记录状态为Terminated
    AND o.STATUS = 'Terminated'
    -- 该员工仅有1条职业记录
    AND o.total_occ_count = 1
    -- 该员工无Active状态的职业记录
    AND o.active_occ_count = 0

写法2（聚合分组版本，兼容性更高，支持所有主流数据库） #

SELECT 
    o.STATUS AS OCCUPATION_STATUS,
    p.STATUS AS PERSON_STATUS,
    p.UPID
FROM PERSONS p
INNER JOIN OCCUPATION o ON p.QSID = o.PERSONID
-- 预过滤符合条件的员工ID
INNER JOIN (
    SELECT PERSONID
    FROM OCCUPATION
    GROUP BY PERSONID
    HAVING 
        COUNT(*) = 1 
        AND MAX(STATUS) = 'Terminated' 
        AND MIN(STATUS) = 'Terminated'
) valid_emp ON o.PERSONID = valid_emp.PERSONID
WHERE 
    p.STATUS = 'Active'
    AND o.STATUS = 'Terminated'

逻辑说明 #

两种写法都满足以下筛选规则：

• PERSONS表中员工状态为Active
• 对应员工在OCCUPATION表中仅有1条职业记录，且该记录状态为Terminated
• 自动排除同时存在Active和Terminated状态职业记录的员工

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