从其他表(如分布)更新表值

Question

我有一个table 1，我必须从其他桌子table 2分配奖励，但要以其他桌子行的先到先发的方式分配奖励。

Table 1
ATTIME    | Absent | LeaveType
-----------------------------
2019-01-01| 1      | 
2019-01-02| 1      | 
2019-01-03| 1      | 
2019-01-04| 1      | 
2019-01-05| 1      | 
2019-01-06| 1      | 
Table 2
LeaveType | Total  
-------------------
Casual    | 3   
Sick      | 2

我已经通过使用光标实现了它，但想要一个设置基础的更新查询或任何其他优化我的执行计划的选项，

最终结果将是....

Table 1
ATTIME    | Absent | LeaveType
-----------------------------
2019-01-01| 1      | CL
2019-01-02| 1      | CL
2019-01-03| 1      | CL
2019-01-04| 1      | SL
2019-01-05| 1      | SL
2019-01-06| 1      |

Answer 1

在受支持的 SQL Server 版本中，您将使用累积总和并row_number()：

with toupdate as (
select t1.*,
row_number() over (order by attime) as seqnum
from table1 t1
)
update toupdate
set leavetype = t2.leavetype
from (select t2.*,
sum(total) over (order by leavetype) as runningtotal
from table2 t2
) t2
where toupdate.seqnum between t2.runningtotal + 1 - total and t2.runningtotal;

在古老的、不受支持的 SQL Server 版本中，累积总和更加繁琐。 一种方法使用相关的子查询：

with toupdate as (
select t1.*,
row_number() over (order by attime) as seqnum
from table1 t1
)
update toupdate
set leavetype = t2.leavetype
from (select t2.*,
(select sum(total)
from table2 tt2
where tt2.leavetype <= t2.leavetype
) as runningtotal
from table2 t2
) t2
where t1.seqnum between t2.runningtotal + 1 - total and t2.runningtotal;