SQL Server 2012更新表速率基于表2中的速率

I have two tables and below is the example

我有两个表，下面就是一个例子

Table1
KEY     BEGIN_DATE         TOTAL_RATE    
1       1974-01-01        3    
1       1981-01-01        3    
1       1983-01-01        4    
1       1985-07-01        4        
1       1989-10-01        7    
1       1990-07-01        10    
1       1997-10-01        11    
1       2008-04-01        13

TABLE2

表二

KEY     END_DATE          RATE_REDUCED    
1       1989-09-30       2    
1       1997-09-31       4

From Table 2 if key matches then we need to reduce TOTALRATE from Table 1 with RATEREDUCED in Table 2 where BEGINDATE > ENDDATE and it should happen till the end of table 2 ENDDATE

从表2中，如果键匹配，那么我们需要从表1中减少TOTALRATE，并在表2中减少RATEREDUCED，其中从开始日期> ENDDATE，直到表2 ENDDATE结束

EXPECTED RESULTS/UPDATE TO TABLE 1:
RESULT

预期结果/更新到表1:结果。

KEY     BEGIN_DATE        NEW_RATE    
1       1974-01-01       3    
1       1981-01-01       3    
1       1983-01-01       4    
1       1985-07-01       4    
1       1989-10-01       7  - 2      = 5 (Date is Greater than 1989-09-30)     
1       1990-07-01       10 - 2      = 8 (Date is Greater than 1989-09-30)    
1       1997-10-01       11 - 2 - 4  = 5 (Date is Greater than two dates)    
1       2008-04-01       13 - 2 - 4  = 7 (Date is Greater than two dates)

I have many keys in table two and table one. Is update with join possible

我有许多钥匙在表二和表一。是否可以使用join进行更新

Thanks in advance

谢谢提前

3 个解决方案

#1

I'm not sure about the difference in performance between using a join or using a subquery. Maybe you can try out the solutions mentioned in the other answers and compare it to a (simpler?) subquery approach:

我不确定使用连接或使用子查询的性能差异。也许你可以尝试其他答案中提到的解决方案，并将其与(更简单的?)子查询方法进行比较:

update table1
set total_rate = total_rate - 
    (select COALESCE(sum(new_rate),0)
     from table2
     where begin_date > end_date
       and table1.key = table2.key)

#2

Similar to Gordon's, here we use use an Update in a CROSS APPLY. This approach will only update the records which match the criteria

类似于Gordon的，这里我们使用交叉应用中的更新。这种方法只更新符合条件的记录

Update Table1 Set TOTAL_RATE=TOTAL_Rate-B.Adj
 From  Table1 A
 Cross Apply (
              Select Adj=sum(RATE_REDUCED) 
               From  Table2 
               Where END_DATE<=A.BEGIN_DATE and [Key]=A.[Key] 
              ) B
 Where B.Adj is not NULL

The Updated Table1 Looks like this now

更新后的表1现在是这样的

KEY BEGIN_DATE  TOTAL_RATE
1   1974-01-01  3
1   1981-01-01  3
1   1983-01-01  4
1   1985-07-01  4
1   1989-10-01  5
1   1990-07-01  8
1   1997-10-01  5
1   2008-04-01  7

#3

This looks like a good application of outer apply:

这看起来是一个很好的外应用:

select t1.*,
       (t1.total_rate - coalesce(t2.rate_reduced, 0)) as total_rate
from table1 t1 outer apply
     (select sum(t2.rate_reduced) as rate_reduced
      from table2 t2
      where t1.begin_date > t2.end_date and
            t1.key = t2.key
     ) t2;

EDIT:

编辑:

If you want to turn this into an update, that is easy:

如果你想把它变成一个更新，那很简单:

update t1 
    set total_rate = (t1.total_rate - coalesce(t2.rate_reduced, 0)) 
from table1 t1 outer apply
     (select sum(t2.rate_reduced) as rate_reduced
      from table2 t2
      where t1.begin_date > t2.end_date and
            t1.key = t2.key
     ) t2;

#1