用于运行整体解决方案的不熟悉的SQL UPDATE语句

I came across the following SQL UPDATE statement that calculates a running total column in a table:

我遇到了以下SQL UPDATE语句，该语句计算表中的运行总列数：

UPDATE N1 SET
    RunningTotal = (SELECT SUM (SubTotal)
                    FROM #Sales X1
                    WHERE
                        N1.FiscalYear = X1.FiscalYear AND
                        X1.OrderNumber <= N1.OrderNumber)
FROM
    #Sales N1

I have not seen this type of pattern before, and because of the difficulty involved in searching for SQL statements, I have not been able to find an explanation. Specifically, I'd like to know how the above statement is able to update the entire table; how is the looping happening?

我之前没有见过这种类型的模式，由于搜索SQL语句的困难，我一直无法找到解释。具体来说，我想知道上面的语句如何能够更新整个表;循环是如何发生的？

Note: The statement works fine; the before and after results as seen in SSMS is as follows:

注意：声明工作正常; SSMS中的前后结果如下：

(I am using SQL Server 2017 Community edition on Windows 10 x64.)

（我在Windows 10 x64上使用SQL Server 2017社区版。）

3 个解决方案

#1

It's not really looping, but I'll explain:

这不是真正的循环，但我会解释：

UPDATE N1 SET
    RunningTotal = ...
FROM #Sales N1

It's updating the whole table because of this. There is no where clause on the update, so every single row will be updated no matter what. I personally prefer this style of aliasing the table and using UPDATE against the alias because when you have complicated updates, it can make viewing the change easier.

因此，它正在更新整个表格。更新中没有where子句，因此无论如何都会更新每一行。我个人更喜欢这种表格别名和对别名使用UPDATE，因为当你有复杂的更新时，它可以更容易地查看更改。

The inner part:

内部部分：

SELECT SUM (SubTotal)
FROM #Sales X1
WHERE N1.FiscalYear = X1.FiscalYear AND
   X1.OrderNumber <= N1.OrderNumber

Gets the sum for of sales amounts each year prior to and equal to the order being processed. It isn't looping in code; it's really running a subquery per row or data being returned.

获取每年之前和等于正在处理的订单的销售额总和。它不是在代码中循环;它确实每行运行一个子查询或返回数据。

#2

Your update statement simply sum all rows before current OrderNumber.

您的update语句只是对当前OrderNumber之前的所有行求和。

Let me show you another approach:

让我告诉你另一种方法：

create table tbl (FiscalYear int, OrderDate date, OrderNumber int, SubTotal decimal(10,2), RunningTotal decimal(10,2));
insert into tbl values
(2011, '20110531', 1, 5000.02, null),
(2011, '20110531', 2, 1000.15, null),
(2011, '20110531', 3,  700.25, null),
(2011, '20110531', 4,  225.02, null),
(2011, '20110531', 5, 1258.25, null),
(2011, '20110531', 6, 1000.00, null),
(2011, '20110531', 7,  695.20, null),
(2011, '20110531', 8,  789.25, null),
(2011, '20110531', 9, 2125.02, null);
GO

The CTE query calculates cumulative totals and second one update your table.

CTE查询计算累计总数，第二个更新表。

;with x as 
(
    select FiscalYear,
           OrderDate,
           OrderNumber,
           SubTotal,
           sum(SubTotal) over (partition by FiscalYear 
                               order by FiscalYear, OrderDate, OrderNumber) as CumTotal
    from   tbl
)
update t
set    RunningTotal = CumTotal
from   tbl t
join   x
on     x.Fiscalyear = t.FiscalYear
and    x.OrderDate = t.OrderDate
and    x.OrderNumber = t.OrderNumber;
GO

9 rows affected

select * from tbl;
GO

FiscalYear | OrderDate           | OrderNumber | SubTotal | RunningTotal
---------: | :------------------ | ----------: | :------- | :-----------
      2011 | 31/05/2011 00:00:00 |           1 | 5000.02  | 5000.02     
      2011 | 31/05/2011 00:00:00 |           2 | 1000.15  | 6000.17     
      2011 | 31/05/2011 00:00:00 |           3 | 700.25   | 6700.42     
      2011 | 31/05/2011 00:00:00 |           4 | 225.02   | 6925.44     
      2011 | 31/05/2011 00:00:00 |           5 | 1258.25  | 8183.69     
      2011 | 31/05/2011 00:00:00 |           6 | 1000.00  | 9183.69     
      2011 | 31/05/2011 00:00:00 |           7 | 695.20   | 9878.89     
      2011 | 31/05/2011 00:00:00 |           8 | 789.25   | 10668.14    
      2011 | 31/05/2011 00:00:00 |           9 | 2125.02  | 12793.16

db<>fiddle here

db <>在这里小提琴

#3

The best solution to your problem is an updatable CTE:

您的问题的最佳解决方案是可更新的CTE：

WITH toupdate AS 
      (SELECT S.*,
              SUM(SubTotal) OVER (PARTITION BY FiscalYear ORDER BY OrderNumber) AS new_RunningTotal
         FROM #Sales S
      )
UPDATE toupdate
    SET RunningTotal = new_RunningTotal;

This has no joins or correlated subqueries. In general, window functions are going to be much faster than the correlated subquery equivalent. Updatable CTEs are a very nice feature of SQL Server that saves you from an additional JOIN.

这没有连接或相关子查询。通常，窗口函数将比相关的子查询等价物快得多。可更新的CTE是SQL Server的一个非常好的功能，可以使您免于额外的JOIN。

#1