具有累积值的SQL更新表

试试这个( Fiddle demo )

DECLARE @Movement INT , @OnHandRunning INT  

;WITH CTE AS
(
    SELECT TOP 100 percent DaysMovement, OnHand 
    FROM Table1
    ORDER BY [StockCode], [Date] DESC
)
UPDATE CTE SET @OnHandRunning = OnHand = COALESCE(@OnHandRunning - @Movement, OnHand),
               @Movement = DaysMovement

更新: 对于多个 StockCodes 您可以像下面那样修改上面的查询( Fiddle demo 2 ):

DECLARE @Movement INT , @OnHandRunning INT, @StockCode VARCHAR(10) = '' 

;WITH CTE AS
(
    SELECT TOP 100 percent DaysMovement, OnHand, StockCode  
    FROM Table1
    ORDER BY [StockCode],[Date] DESC
)
UPDATE CTE SET @OnHandRunning = OnHand = 
       CASE WHEN @StockCode<> StockCode THEN OnHand ELSE @OnHandRunning - @Movement END,
       @Movement = DaysMovement,
       @StockCode = StockCode

这是有效的,但不知道它与光标相比的性能如何?

--Data
DECLARE @Table TABLE (
    [Date] DATE,
    StockCode VARCHAR(50),
    DaysMovement INT,
    OnHand INT);
INSERT INTO @Table VALUES ('20140729', 'SC123', 30, 500);
INSERT INTO @Table VALUES ('20140728', 'SC123', 15, NULL);
INSERT INTO @Table VALUES ('20140727', 'SC123', 0, NULL);
INSERT INTO @Table VALUES ('20140726', 'SC123', 4, NULL);
INSERT INTO @Table VALUES ('20140725', 'SC123', -2, NULL);
INSERT INTO @Table VALUES ('20140724', 'SC123', 0, NULL);

--Query
SELECT 
    t1.[Date], 
    t1.StockCode, 
    t1.DaysMovement, 
    CASE WHEN t1.OnHand IS NULL THEN MAX(t2.OnHand) - SUM(t2.DaysMovement) ELSE t1.OnHand END AS OnHand 
FROM 
    @Table t1 
    LEFT JOIN @Table t2 ON t1.[Date] < t2.[Date]
GROUP BY 
    t1.[Date], 
    t1.StockCode, 
    t1.DaysMovement, 
    t1.OnHand
ORDER BY 
    t1.[Date] DESC;

结果如下:

Date        StockCode   DaysMovement    OnHand
2014-07-29  SC123       30              500
2014-07-28  SC123       15              470
2014-07-27  SC123       0               455
2014-07-26  SC123       4               455
2014-07-25  SC123       -2              451
2014-07-24  SC123       0               453

如果你不能使用 LAG ,您可以尝试递归CTE:

DECLARE @table TABLE ([Date] VARCHAR(100), StockCode VARCHAR(100), DaysMovement INT, OnHand INT)


INSERT INTO @table SELECT '29-Jul', 'SC123', 30, 500
INSERT INTO @table SELECT '28-Jul', 'SC123', 15, NULL
INSERT INTO @table SELECT '27-Jul', 'SC123', 0, NULL
INSERT INTO @table SELECT '26-Jul', 'SC123', 4, NULL
INSERT INTO @table SELECT '25-Jul', 'SC123', -2, NULL
INSERT INTO @table SELECT '24-Jul', 'SC123', 0, NULL


INSERT INTO @table SELECT '19-Jul', 'SC1234', 30, 500
INSERT INTO @table SELECT '18-Jul', 'SC1234', 15, NULL
INSERT INTO @table SELECT '17-Jul', 'SC1234', 0, NULL
INSERT INTO @table SELECT '16-Jul', 'SC1234', 4, NULL
INSERT INTO @table SELECT '15-Jul', 'SC1234', -2, NULL
INSERT INTO @table SELECT '14-Jul', 'SC1234', 0, NULL


;WITH addRowID As (

    SELECT [Date], StockCode, DaysMovement, OnHand, ROW_NUMBER() OVER (PARTITION BY StockCode ORDER BY StockCode, [Date] DESC) AS ROWID
    FROM @table

), CTE AS (
    SELECT [Date], StockCode, DaysMovement, OnHand, ROWID
    FROM addRowID 
    WHERE OnHand IS NOT NULL AND ROWID = 1

    UNION ALL

    SELECT D.[Date], D.StockCode, D.DaysMovement, C.OnHand - C.DaysMovement AS OnHand, D.ROWID
    FROM CTE AS C
    INNER JOIN addRowID AS D
        ON D.StockCode = C.StockCode
        AND D.ROWID = C.ROWID + 1
    WHERE D.OnHand IS NULL

)

SELECT *
FROM CTE
ORDER BY [DATE] DESC

而不是最后 SELECT 您可以放置更新。不确定性能。。。。

假设您没有使用sqlserver 2012+,这将使这更加容易。试试这个。它将更新您的表:

DECLARE @t TABLE(Date date, StockCode char(5), DaysMovement int, OnHand int)

INSERT @t VALUES
('29-Jul-2014','SC123',30,500),
('28-Jul-2014','SC123',15,NULL),
('27-Jul-2014','SC123',0 ,NULL),
('26-Jul-2014','SC123',4 ,NULL),
('25-Jul-2014','SC123',-2,NULL),
('24-Jul-2014','SC123',0 ,NULL)

;WITH cte as
(
  SELECT
    Date, 
    StockCode,
    DaysMovement,
    OnHand, 
    max(case when Date = '2014-07-29' THEN OnHand END) over() BaseOnhand, 
    Calc.MovementChange
  FROM @t t
  CROSS APPLY
  (SELECT
     coalesce(SUM(DaysMovement), 0) MovementChange 
   FROM @t
   WHERE
     Date between DateAdd(Day, 1, t.Date) and '2014-07-29'
  ) calc
)
UPDATE CTE SET OnHand = BaseOnhand - MovementChange

SELECT * FROM @t

结果:

Date        StockCode DaysMovement OnHand
2014-07-29  SC123     30           500
2014-07-28  SC123     15           470
2014-07-27  SC123     0            455
2014-07-26  SC123     4            455
2014-07-25  SC123     -2           451
2014-07-24  SC123     0            453

如果您使用的是SQL Server 2008或更早版本 JOIN 是解决这个问题的简单方法

SELECT a.[Date], a.StockCode, a.DaysMovement
     , OnHand = MAX(b.OnHand) - SUM(b.DaysMovement) + a.DaysMovement
FROM   Table1 a
       LEFT JOIN Table1 b ON a.StockCode = b.StockCode AND b.[Date] >= a.[Date]
GROUP BY a.[Date], a.StockCode, a.DaysMovement
ORDER BY a.[Date] DESC

SQLFiddle Demo

如果您有SQL Server 2012或更高版本 ORDER 在窗口功能中使查询更容易

SELECT [Date], StockCode, DaysMovement
     , OnHand = MAX(OnHand) OVER (PARTITION BY StockCode)
              - SUM(DaysMovement) OVER (PARTITION BY StockCode 
                                        ORDER BY [Date] Desc)
              + DaysMovement
FROM   Table1

SQLFiddle Demo

两个查询使用相同的逻辑:对于每一行 OnHand (使用 MAX 并删除当前之后每天的移动,这样做也会删除当前的移动,因此将其相加。此逻辑避免在每个 StockCode .

这个 UPDATE 使用 WITH 如果您使用的是SQLServer2005或更高版本(在此我使用的是SQLServer 2012或更高的版本)

WITH A AS (
  SELECT [Date], StockCode, DaysMovement
       , OnHand = MAX(OnHand) OVER (PARTITION BY StockCode)
                - SUM(DaysMovement) OVER (PARTITION BY StockCode 
                                          ORDER BY [Date] Desc)
                + DaysMovement
  FROM   Table1
)
UPDATE Table1 SET
  OnHand = A.OnHand
FROM Table1
     INNER JOIN A ON Table1.StockCode = a.StockCode AND Table1.[Date] = a.[Date]