Oracle 根据日期范围查找正确的值。

我有3个表，一个有工资信息 (工资单)，一个有工资期限 (erpaydates) 和一个退休缴费百分比 (planecontrpct)。我正在尝试找到一种方法，以基于planecontrpct.effdate为每个支付期 (erpaydates.enddate) 选择员工的缴费百分比 (planecontrpct.contrpct)，而无需创建重叠记录。我根据表中的数据寻找的结果集如下。我已经完成了查询的基本部分，但是我不能在每个支付期间只获得一条记录。

select payroll.ssnum, erpaydates.enddate, payroll.salamt, planeecontrpct.contrpct
from payroll, erpaydates, planeecontrpct
where payroll.erid = erpaydates.erid and payroll.payfreqcd = erpaydates.payfreqcd and payroll.payfreqseqnum = erpaydates.payfreqseqnum 
and payroll.paypernum = erpaydates.paypernum and payroll.ssnum = planeecontrpct.ssnum
and erpaydates.begindate between planeecontrpct.effdate and erpaydates.enddate
order by payroll.ssnum, erpaydates.enddate;



payroll.ssnum  erpaydates.enddate   payroll.salamt  planeecontrpct.contrpct
111111111      15-jan-2017          1000            5
111111111      01-feb-2017          1000            5
111111111      15-feb-2017          1000            5
111111111      01-mar-2017          1000            10
111111111      15-mar-2017          1000            10
222222222      15-jan-2017          1000            8
222222222      01-feb-2017          1000            0
222222222      15-feb-2017          1000            0
222222222      01-mar-2017          1000            7
222222222      15-mar-2017          1000            7

确定缴费百分比与支付期如何一致的逻辑到底是什么？

您的查询的问题是它将erpaydates的开始日期与其结束日期进行比较。想必这永远是真的!

所以你可以简化:

and erpaydates.begindate between planeecontrpct.effdate and erpaydates.enddate

致:

and erpaydates.begindate >= planeecontrpct.effdate

希望这使问题更加清晰: 您正在加入当前时期之前开始的所有贡献!

您可以通过将planecontrpct.effdate与付款结束日期进行比较来克服此问题:

select payroll.ssnum,
       erpaydates.enddate,
       payroll.salamt,
       planeecontrpct.contrpct
from payroll
join erpaydates
on   payroll.erid = erpaydates.erid
and payroll.payfreqcd = erpaydates.payfreqcd
and payroll.payfreqseqnum = erpaydates.payfreqseqnum
and payroll.paypernum = erpaydates.paypernum
left join  planeecontrpct
on payroll.ssnum = planeecontrpct.ssnum
and erpaydates.begindate <= planeecontrpct.effdate 
and planeecontrpct.effdate <= erpaydates.enddate
order by payroll.ssnum,
  erpaydates.enddate;

SSNUM      ENDDATE               SALAMT  CONTRPCT  
111111111  15-JAN-2017 00:00:00  1000    5         
111111111  01-FEB-2017 00:00:00  1000              
111111111  15-FEB-2017 00:00:00  1000              
111111111  01-MAR-2017 00:00:00  1000    10        
111111111  15-MAR-2017 00:00:00  1000              
222222222  15-JAN-2017 00:00:00  1000    8         
222222222  01-FEB-2017 00:00:00  1000    0         
222222222  15-FEB-2017 00:00:00  1000              
222222222  01-MAR-2017 00:00:00  1000    7         
222222222  15-MAR-2017 00:00:00  1000

但我猜那不是你真正想要的。我希望你想填补 “缺失” 的贡献。

您可以使用last_value，忽略null来执行此操作:

select payroll.ssnum,
       erpaydates.enddate,
       payroll.salamt,
       last_value(planeecontrpct.contrpct) ignore nulls over (
         partition by payroll.ssnum order by planeecontrpct.effdate 
       ) contrpct
from payroll
join erpaydates
on   payroll.erid = erpaydates.erid
and payroll.payfreqcd = erpaydates.payfreqcd
and payroll.payfreqseqnum = erpaydates.payfreqseqnum
and payroll.paypernum = erpaydates.paypernum
left join  planeecontrpct
on payroll.ssnum = planeecontrpct.ssnum
and erpaydates.begindate <= planeecontrpct.effdate 
and planeecontrpct.effdate <= erpaydates.enddate
order by payroll.ssnum,
  erpaydates.enddate;

SSNUM      ENDDATE               SALAMT  CONTRPCT  
111111111  15-JAN-2017 00:00:00  1000    5         
111111111  01-FEB-2017 00:00:00  1000    10        
111111111  15-FEB-2017 00:00:00  1000    10        
111111111  01-MAR-2017 00:00:00  1000    10        
111111111  15-MAR-2017 00:00:00  1000    10        
222222222  15-JAN-2017 00:00:00  1000    8         
222222222  01-FEB-2017 00:00:00  1000    0         
222222222  15-FEB-2017 00:00:00  1000    7         
222222222  01-MAR-2017 00:00:00  1000    7         
222222222  15-MAR-2017 00:00:00  1000    7

但这是正确的吗？

我不确定。

员工111111111从2月15日开始的2月18日部分开始有一个新的费率

这应该分成两个时期 (2月15日-2月18日和2月18日-3月1日) 吗？还是保持一体？

如果是后者，则SQL会变得更加复杂...