代码之家 › 专栏 › 技术社区 › Sabuncu

不熟悉用于运行total解决方案的sql update语句

running-total sql-server sql

Sabuncu · 技术社区 · 7 年前

我遇到了以下sql update语句,该语句计算表中正在运行的total列:

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

我以前从未见过这种类型的模式,而且由于在搜索sql语句时遇到的困难,我无法找到解释。具体来说,我想知道上面的语句是如何更新整个表的;循环是如何发生的?

注:该语句工作正常;SSMS中的前后结果如下:

(我正在Windows 10 x64上使用SQL Server 2017社区版。)

3 回复 | 直到 7 年前

Sabuncu 7 年前

不是真的循环,但我会解释:

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

因为这个它正在更新整个表。更新中没有where子句,因此无论发生什么,每一行都将被更新。我个人更喜欢这种形式的别名表,并使用更新对别名,因为当你有复杂的更新,它可以使查看变化更容易。

内部部分:

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

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

McNets 7 年前

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

让我给你展示另一种方法:

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

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<gt;小提琴 here

Sabuncu 7 年前

解决问题的最佳方法是使用可更新的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;

这没有联接或关联的子查询。一般来说,窗口函数要比相关的子查询快得多。可更新的cte是sql server的一个非常好的特性,它将您从 JOIN .