如何根据字段的总和更新记录，然后使用总和在sql中计算新值

Question

以下是我试图通过迭代记录来完成的操作。

如果可能的话，我希望有一个更优雅的解决方案，因为我确信这不是在 sql 中执行此操作的最佳方法。

set @counter = 1 

declare @totalhrs dec(9,3), @lastemp char(7), @othrs dec(9,3) 

while @counter <= @maxrecs 
begin 
  if exists(select emp_num from #tt_trans where id = @counter) 
  begin 
    set @nhrs = 0 
    set @othrs = 0 

    select @empnum = emp_num, @nhrs = n_hrs, @othrs = ot_hrs 
    from #tt_trans 
    where id = @counter 

    if @empnum = @lastemp 
    begin 
      set @totalhrs = @totalhrs + @nhrs 

      if @totalhrs > 40 
      begin 
        set @othrs = @othrs + @totalhrs - 40 
        set @nhrs = @nhrs - (@totalhrs - 40) 
        set @totalhrs = 40 
      end 
    end 
    else 
    begin 
       set @totalhrs = @nhrs 
       set @lastemp = @empnum 
    end 

    update #tt_trans 
    set n_hrs = @nhrs, 
        ot_hrs = @othrs 
    where id = @counter and can_have_ot = 1 
  end 

  set @counter = @counter + 1 
end

谢谢

Answer 1

这很接近您想要的，但是一旦您回答了我关于您真正想要实现的目标的评论，就需要进行一些调整。

update #tt_trans 
    set n_hrs = CASE WHEN T2.totHrs>40 
                     THEN 40 
                     ELSE T2.totHrs END,
    ot_hrs= CASE WHEN T2.totHrs>40 
                 THEN T2.totHrs-40 
                 ELSE 0 END
FROM  #tt_trans trans T1
INNER JOIN (SELECT SUM(@nhrs) totHrs, EmpNum 
           FROM #tt_trans 
           WHERE can_have_ot=1 
           GROUP BY EmpNum) T2 ON (T1.EmpNum=T2.EmpNum)

WHERE can_have_ot = 1 

Answer 2

看起来您正在迭代，因为您必须跨多个记录跟踪给定员工的总工时。您可以改为使用内部选择来汇总每个员工的小时数，并将该选择加入到 #tt_trans 表中。从该连接写入更新，并将逻辑放入更新列的 CASE 语句中。

Answer 3

update #tt_trans
    set n_hrs = case 
    when t2.totHrs > 40
    then n_hrs - (t2.totHrs -40)
    else n_hrs
    end,
    ot_hrs = case 
    when t2.totHrs > 40 
    then ot_hrs + (t2.totHrs -40)
    else ot_hrs
    end
    from #tt_trans t1
    join (select sum (n_hrs) as totHrs, emp_num from #tt_trans where can_have_ot =1 group by emp_num) t2 on t1.emp_num = t2.emp_num where t1.post_date = (select max(post_date)from #tt_trans where emp_num = t1.emp_num)

基本上我修改了该期间最后一个日期的时间并进行了相应调整。感谢两位的回复。这两个回复都让我想到了我的。