如何改写SQL考勤查询实现多类工时分类汇总且不使用子查询

方案说明 #

可以在不使用子查询的情况下实现该需求，最优实现方式是使用条件聚合逻辑，仅通过CASE WHEN搭配聚合函数+分组即可完成，无需嵌套子查询。

完整实现SQL（已修复原SQL中的表别名笔误） #

SELECT 
    papf.person_number,
    SUM(CASE WHEN atrb.attribute_category = 'Overtime' THEN atrb.measure ELSE 0 END) AS Overtime_measure_hours,
    SUM(CASE WHEN atrb.attribute_category = 'Regular Pay' THEN atrb.measure ELSE 0 END) AS Regular_Measure_hours,
    MAX(CASE WHEN atrb.attribute_category NOT IN ('Overtime','Regular Pay') THEN atrb.attribute_category END) AS Others_code,
    SUM(CASE WHEN atrb.attribute_category NOT IN ('Overtime','Regular Pay') THEN atrb.measure ELSE 0 END) AS Others_measure
FROM 
    per_all_people_F papf,
    hwm_tm_rec rec,
    fusion.hwm_tm_rep_atrbs atrb,
    fusion.hwm_tm_rep_atrb_usages ausage,
    hwm_tm_statuses status
WHERE 1=1
    AND atrb.tm_rep_atrb_id = ausage.tm_rep_atrb_id
    AND ausage.usages_source_id = rec.tm_rec_id
    AND ausage.usages_source_version = rec.tm_rec_version
    AND status.tm_bldg_blk_id = REC.tm_rec_id
    AND status.tm_bldg_blk_version = REC.tm_rec_version
    AND REC.tm_rec_type IN ( 'RANGE', 'MEASURE' )
    AND papf.person_number = '101928'
    AND Trunc (status.date_to) = To_date ('31/12/4712', 'DD/MM/YYYY')
    AND atrb.attribute_category in( 'Overtime','Regular Pay', 'Double_Time')
    -- 修复原SQL的别名笔误：原sh21无定义，替换为关联表别名rec
    AND Trunc (rec.start_time) between trunc(:P_From_Date) and trunc(:P_To_Date)
GROUP BY papf.person_number

逻辑说明 #

• 对Overtime和Regular Pay两类时长，通过CASE WHEN判断匹配后累加求和，直接得到对应总时长
• 其他类型的编码用MAX聚合获取（如果存在多个其他类型，可替换为LISTAGG函数拼接多个编码），时长同样用条件求和得到总时长
• 最后按人员编号分组，即可得到每个人员的汇总统计结果，完全不需要子查询支持，执行效率更高。

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