SQL查询员工考勤打卡班次类型时Others_code列输出异常如何解决

你遇到的问题根源是分组维度设置错误：你当前仅按person_number、file_id分组，每个分组只会返回1行结果，因此MAX(Others_code)只会取到排序优先级最高的单个班次类型，无法拆分多班次独立行；去掉聚合后分组维度不匹配，自然会出现字段错位的问题。

你可以用窗口函数实现需求，无需嵌套多层子查询关联，修正后的SQL如下：

SELECT
  person_number,
  -- 窗口函数按人员+文件维度聚合，所有行共享当前维度总加班/正常工时
  SUM(CASE WHEN attribute_category = 'Overtime' THEN measure END) OVER (PARTITION BY person_number, file_id) AS Overtime_measure_hours,
  SUM(CASE WHEN attribute_category LIKE 'Regular P%' THEN measure END) OVER (PARTITION BY person_number, file_id) AS Regular_Measure_hours,
  attribute_category AS Others_code,
  measure AS Others_measure,
  file_id
FROM (
  SELECT 
    papf.person_number,
    rec.file_id,
    atrb.attribute_category,
    atrb.measure
  FROM hwm_tm_rec AS rec
  -- 修正原交叉连接笛卡尔积问题，补充人员关联条件，可按实际字段调整
  JOIN per_all_people_F AS papf 
    ON rec.person_id = papf.person_id 
    AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
  JOIN fusion.hwm_tm_rep_atrb_usages AS ausage
    ON ausage.usages_source_id = rec.tm_rec_id
    AND ausage.usages_source_version = rec.tm_rec_version
  JOIN fusion.hwm_tm_rep_atrbs AS atrb
    ON atrb.tm_rep_atrb_id = ausage.tm_rep_atrb_id
  JOIN hwm_tm_statuses AS status
    ON status.tm_bldg_blk_id = rec.tm_rec_id
    AND status.tm_bldg_blk_version = rec.tm_rec_version
  WHERE 1=1
    AND papf.person_number = '11'
    AND rec.tm_rec_type IN ('RANGE', 'MEASURE') 
    AND TRUNC(status.date_to) = TO_DATE('31/12/4712', 'DD/MM/YYYY')
    AND atrb.attribute_category IN ('Overtime','Regular', 'Double','Weekend shift','Extra shift')
    -- 修正原笔误 sh21.start_time 为 rec.start_time
    AND TRUNC(rec.start_time) BETWEEN TRUNC(:P_From_Date) AND TRUNC(:P_To_Date)
) t
-- 过滤仅保留其他班次行，丢弃加班、正常工时的独立行
WHERE attribute_category IN ('Double','Weekend shift','Extra shift')

逻辑说明 #

• 窗口函数SUM() OVER(PARTITION BY person_number, file_id)会按人员+文件维度统计总时长，不需要对班次字段做聚合，因此每行都能拿到统一的总加班、总正常工时数值
• 最后过滤出仅属于其他班次的行，即可实现每个班次单独占一行，前两列固定为总时长的预期效果

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