DB2中SQL结合GROUP BY查询新增2020年2月缺勤标识字段实现方案

实现思路 #

• 左连接预过滤后的缺勤表（absent），避免匹配到不符合条件的缺勤记录导致薪资表数据重复、工时统计错误
• 预过滤逻辑：仅保留2020年2月存在时间交集、缺勤编码CODE2为119或120的记录，按人员ID去重取首个符合条件的缺勤编码
• 用COALESCE函数对无匹配缺勤记录的人员返回默认值NOTHING

修改后完整SQL #

SELECT
    S.PERSONID, 
    P.NAME,
    E.POSITION, 
    SUM(S.QUANTITY) AS QUANTITY,
    COALESCE(A.CODE2, 'NOTHING') AS ABSENT
FROM 
    SALARY_ITEMS  S  
LEFT JOIN 
    PERSON P ON S.PERSONID = P.PERSONID
LEFT JOIN  
    EMPLOYMENT E ON E.EMPLOYMENT_REF = S.EMPLOYMENT_REF -- 注意原字段名的空格要和实际表结构一致
-- 新增关联预过滤后的缺勤表
LEFT JOIN (
    SELECT DISTINCT PERSONID, CODE2
    FROM absent
    WHERE 
        CODE2 IN ('120', '119')
        -- 判断缺勤时间和2020年2月存在交集
        AND "FROM" <= '2020-02-29'
        AND "END" >= '2020-02-01'
) A ON S.PERSONID = A.PERSONID
WHERE
    S.GROUP1 IN ('400', '440', '450', '470', '640') 
    AND S.CODE IN ('11101', '11111', '11121', '11131', '11141')
    AND S."FROM" >= '2020-02-01'
    AND S."END" <= '2020-02-29'
    AND E.POSITION IN ('nurse', 'doctor')
    AND (
        SELECT SUM(S2.QUANTITY) AS QUANTITY2
        FROM SALARY_ITEMS S2   
        WHERE 
            S2.GROUP1 IN ('400', '440', '450', '470', '640')
            AND S2.CODE IN ('11101', '11111', '11121', '11131', '11141')
            AND S2."FROM" >= '2020-02-01'
            AND S2."END" <= '2020-02-29'
            AND S.PERSONID = S2.PERSONID
    ) >= 100
GROUP BY  
    S.PERSONID, P.NAME, E.POSITION, A.CODE2

关键说明 #

• FROM、END是DB2保留关键字，查询时用双引号包裹避免语法错误
• 缺勤表预过滤时用DISTINCT按人员ID去重，避免同一人员多条符合条件的缺勤记录导致薪资行重复、工时SUM计算错误
• 如果需要返回所有符合条件的缺勤编码，可将A.CODE2替换为LISTAGG(DISTINCT A.CODE2, ',')（DB2 9.7及以上版本支持），分组条件保留S.PERSONID, P.NAME, E.POSITION即可
• 原SQL中E.POSTION为拼写错误，已修正为POSITION，实际使用时请和表真实字段名对齐

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