sql-server - SQL Server 使用查询计算运行总值

Question

我使用 SQL Server 2012 并有一个如下表:

DECLARE @T TABLE(Id INT, [Type] CHAR(1), Quantity INT, Price MONEY, UnitPrice AS (Price/Quantity))
INSERT INTO @T VALUES 
    (1, 'I', 30, 1500),
    (2, 'O', 5, NULL),
    (3, 'O', 20, NULL),
    (4, 'O', 2, NULL),
    (5, 'I', 10, 2500),
    (6, 'I', 8, 1000),
    (7, 'O', 3, NULL),
    (8, 'O', 10, NULL),
    (9, 'I', 12, 3600)

在我的表中，我有一个带有值的类型列('I'和'O')我有'I'类型记录的单价和最后使用的'O'类型记录'I'键入记录值我要计算 RunningTotalPrice(数量总和*每行的单价)。

以下代码计算 RunningTotalQuantity:

SELECT *, 
        SUM(CASE WHEN [Type] = 'I' Then Quantity ELSE -Quantity END)OVER (ORDER BY Id) AS QuantityRunningTotal
FROM @T

这个查询的结果是:

Id  Type    Quantity    Price   UnitPrice   QuantityRunningTotal
1   I       30          1500/00 50/00       30
2   O       5           NULL    NULL        25
3   O       20          NULL    NULL        5
4   O       2           NULL    NULL        3
5   I       10          2500/00 250/00      13
6   I       8           1000/00 125/00      21
7   O       3           NULL    NULL        18
8   O       10          NULL    NULL        8
9   I       12          3600/00 300/00      20

我想要下面的结果

Id  Type    Quantity    Price   UnitPrice   QuantityRunningTotal  Price       RunningTotalPrice
1   I       30          1500/00 50/00       30                    1500/00      1500/00
2   O       5           NULL    50/00       25                    250/00       1250/00
3   O       20          NULL    50/00       5                     1000/00      250/00
4   O       2           NULL    50/00       3                     100/00       150/00
5   I       10          2500/00 250/00      13                    2500/00      2650/00
6   I       8           1000/00 125/00      21                    1000/00      3650/00
7   O       3           NULL    125/00      18                    375/00       3275/00
8   O       10          NULL    125/00      8                     1250/00      2025/00
9   I       12          3600/00 300/00      20                    3600/00      5625/00

在此结果中，Null Unitprice Column value with last exists unitprice in before records.并计算价格(数量 * 单价)和计算总价格。

Answer 1

不幸的是 LEAD 和 LAG 函数不能用于最后一个非 NULL 值，所以你需要使用 OUTER APPLY 以获取前一个 UnitPrice 以在类型为“O”的行中使用:

SELECT  t.ID,
        t.[Type],
        t.Quantity,
        t.Price,
        t.UnitPrice, 
        SUM(CASE WHEN t.[Type] = 'I' THEN t.Quantity ELSE -t.Quantity END) OVER (ORDER BY t.Id) AS QuantityRunningTotal,
        CASE WHEN t.[Type] = 'I' THEN t.Price ELSE t.Quantity * p.UnitPrice END AS Price2,
        SUM(CASE WHEN t.[Type] = 'I' THEN t.Price ELSE -t.Quantity * p.UnitPrice END)OVER (ORDER BY t.Id) AS QuantityRunningTotal
FROM    @T AS t
        OUTER APPLY
        (   SELECT  TOP 1 t2.UnitPrice
            FROM    @T AS t2
            WHERE   t2.ID < t.ID
            AND     t2.UnitPrice IS NOT NULL
            ORDER BY t2.ID DESC
        ) AS p;