SQL Server查询计算累加总值

Question

SQL Server查询计算累加总值

sql-serversql-server-2012

3

我使用 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'类型记录的值来计算'O'类型记录的单价。我想要计算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

在这个结果中，空的Unitprice列被赋予了上一条记录中存在的unitprice值。然后计算价格（数量*单价）并计算价格的累积总和。

- mehdi lotfi

我仍然不理解第二个“Price”列。 - Alireza

按数量*单价计算的第二价格，对于类型为'I'的记录，第二价格等于第一价格。 - mehdi lotfi

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GarethD · Accepted Answer

很遗憾，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;