sql-server - SQL Server - 带结转的运行总计

Question

需要以下方面的帮助:

表#Data 包含产品超过 5 天的开盘和收盘库存

表#BackData 包含一些过时的交易

如何使用包含结转的运行总计来更新表#Data

CREATE TABLE #Data (    
    Prod VARCHAR(20)
    ,SDate DATE
    ,OStock INT
    ,CStock INT
    )

CREATE TABLE #BackData (
    Prod VARCHAR(20)
    ,SDate DATE
    ,CStock INT
    )


INSERT INTO #Data
SELECT 'p1', '2016-06-06', 10, 10
UNION ALL
SELECT 'p1', '2016-06-07', 10, 14
UNION ALL
SELECT 'p1', '2016-06-08', 14, 13
UNION ALL
SELECT 'p1', '2016-06-09', 13, 13
UNION ALL
SELECT 'p1', '2016-06-10', 13, 11

INSERT INTO #BackData
SELECT 'p1', '2016-06-06', 2
UNION ALL
SELECT 'p1', '2016-06-07', 4
UNION ALL
SELECT 'p1', '2016-06-09', -1
UNION ALL
SELECT 'p1', '2016-06-10', -2

DROP TABLE #Data
DROP TABLE #BackData

期望的输出:

Prod|   SDate   |OStock |CStock|
p1  |2016-06-06 |10     |12    |
p1  |2016-06-07 |12     |16    |
p1  |2016-06-08 |16     |16    |
p1  |2016-06-09 |16     |15    |
p1  |2016-06-10 |15     |13    |

编辑

这是我在得到答案之前设法编写的内容，使用了两次更新，因为实际表有太多列无法在单个查询中使用。

UPDATE D
SET D.CStock = FL.NewCStock
FROM #Data D
INNER JOIN (
    SELECT DT.Prod
        ,DT.SDate
        ,SUM(IIF(RwNm = 1, DT.CStock, 0) + ISNULL(BD.CStock, 0)) OVER (
            PARTITION BY DT.Prod ORDER BY DT.SDate ROWS UNBOUNDED PRECEDING
            ) NewCStock
    FROM (
        SELECT Prod
            ,SDate
            ,CStock
            ,ROW_NUMBER() OVER (
                PARTITION BY Prod ORDER BY SDate
                ) AS RwNm
        FROM #Data
        ) DT
    LEFT JOIN #BackData BD ON DT.Prod = DT.Prod
        AND BD.SDate = DT.SDate
    ) FL ON D.Prod = FL.Prod
    AND D.SDate = FL.SDate

UPDATE D
SET D.OStock = PV.NewOStock
FROM #Data D
INNER JOIN (
    SELECT Prod
        ,SDate
        ,ISNULL(LAG(CStock) OVER (
                PARTITION BY Prod ORDER BY SDate
                ), CStock) AS NewOStock
    FROM #Data
    ) PV ON D.Prod = PV.Prod
    AND D.SDate = PV.SDate

Answer 1

您可以使用以下查询来更新:

;WITH ToUpdate AS (
   SELECT d1.OStock, d1.CStock,
          COALESCE(LAG(d2.CStock2) OVER (PARTITION BY d2.Prod 
                                         ORDER BY d2.SDate),
                   d1.OStock) AS OStock2,
          d2.CStock2  
   FROM #Data AS d1
   JOIN (
      SELECT d.Prod, d.SDate, d.OStock, d.CStock, 
             COALESCE(t.newCStock, 
                      LAG(t.newCStock) OVER (PARTITION BY d.Prod 
                                             ORDER BY d.SDate)) AS CStock2
      FROM #Data AS d
      LEFT JOIN (
         SELECT bd.Prod, bd.SDate, 
                drn.CStock + SUM(bd.CStock) OVER (PARTITION BY bd.Prod 
                                                  ORDER BY bd.SDate) AS newCStock
         FROM #BackData AS bd
         INNER JOIN (
            SELECT Prod, CStock, 
                   ROW_NUMBER() OVER (PARTITION BY Prod ORDER BY SDate) AS rn
            FROM #Data
         ) AS drn ON bd.Prod = drn.Prod AND drn.rn = 1
      ) AS t ON t.Prod = d.Prod AND t.SDate = d.SDate
   ) AS d2 ON d1.Prod = d2.Prod AND d1.SDate= d2.SDate
)
UPDATE ToUpdate
SET OStock = OStock2,
    CStock = CStock2

这看起来非常复杂，但我想不出任何更简单的事情。

Demo here