如何仅筛选REG与OT工时相等的员工薪资数据？

原SQL仅筛选了earncode为'REG'或'OT'的记录，但未关联同一员工的两类工时记录，也未过滤无对应OT的REG数据，导致不符合需求。以下是两种可行的解决方案：

方法一：自连接匹配 #

通过自连接将同一员工、同班次的REG和OT记录关联，直接筛选工时相等的结果：

select 
    r.empno,
    r.earncode as reg_earncode,
    r.curhrs as reg_curhrs,
    o.earncode as ot_earncode,
    o.curhrs as ot_curhrs,
    r.shfshiftcode,
    r.curamt as reg_curamt,
    o.curamt as ot_curamt
from earnings r
join earnings o 
    on r.empno = o.empno
    and r.shfshiftcode = o.shfshiftcode
    and r.curhrs = o.curhrs
where 
    r.earncode = 'REG'
    and o.earncode = 'OT'
    and r.shfshiftcode like 'N%'
order by r.empno;

方法二：分组筛选+窗口函数 #

先按员工和班次分组，确保每组同时存在REG和OT记录，再筛选工时相等的条目：

with emp_earnings as (
    select 
        empno,
        earncode,
        curhrs,
        curamt,
        shfshiftcode,
        count(distinct earncode) over (partition by empno, shfshiftcode) as code_count,
        max(case when earncode = 'OT' then curhrs end) over (partition by empno, shfshiftcode) as ot_curhrs
    from earnings
    where earncode in ('REG', 'OT')
        and shfshiftcode like 'N%'
)
select empno, earncode, curhrs, curamt, shfshiftcode
from emp_earnings
where code_count = 2  
    and curhrs = ot_curhrs;  

说明 #

• 自连接方法逻辑直观，适合需要同时展示REG和OT字段的场景；
• 窗口函数方法更灵活，便于扩展其他筛选条件。

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