使用当前行中的字段将增加应用到上一行

Question

我有一个像这样设置的临时表：

Type    Rate    TotalCost
----    ----    ---------
Type1   0.01    3276.00
Type2   0.01    3276.00
Type3   0.01    3276.00

但是，我需要获取当前行的速率，将其乘以之前的总计，然后将其添加到当前总计......以得出新的总计！

Type    Rate    TotalCost
----    ----    ---------
Type1   0.01    3276
Type2   0.01    3308.76
Type3   0.01    3341.85

所以 3276 的 1% 是 32.76。

3276 + 32.76 = 3308.76。

3308 的 1% 是 33.08。

3308.76 + 33.08 = 3341.85。

等等。

我尝试创建一个 CTE，就像这样

;with cte 
as 
(   
select Type, Rate, TotalCost, row_number() over (order by SortOrder asc) as RowNum   
from @Types
) 

select cur.RowNum, cur.Rate, prev.Rate, cur.TotalCost, isnull(prev.TotalCost * cur.Rate, cur.TotalCost) as NewTotal 
from cte cur 
left join cte prev on prev.RowNum = cur.RowNum + 1

......但它不起作用:(

任何人都可以帮忙吗？

我正在使用 SQL Server 2005！

提前致谢！

Answer 1

从您的示例结果来看，您似乎正在对所有以前的行进行求和，而不仅仅是最后一行。您可以在没有 CTE 的情况下做到这一点，例如：

declare @YourTable table (type varchar(15), rate float, TotalCost money)
insert @YourTable values
    ('Type1',   0.01,    3276.00),
    ('Type2',   0.01,    3276.00),
    ('Type3',   0.01,    3276.00);

select  cur.Type
,       case 
        when sum(prev.rate) is null then 0 
        else sum(prev.rate * prev.TotalCost) 
        end + cur.TotalCost
from    @YourTable cur
left join 
        @YourTable prev
on      prev.type < cur.type 
group by
        cur.type
,       cur.TotalCost

一个问题是示例数据中没有排序顺序。我在这里按类型排序；我希望您的真实表格中有更好的排序顺序！

另一个问题是只有第一行的 TotalCost 很重要。对于以下行，成本是根据第一行的成本和其他行的费率得出的。

Answer 2

我发现最好的方法是编写一个循环：

DECLARE @Temp TABLE
(
    Id INT IDENTITY(1,1),
    Type VARCHAR(10),
    Rate FLOAT,
    TotalCost MONEY
)

INSERT INTO @Temp (Type, Rate, TotalCost)
VALUES ('Type1', 0.01, 3276.00),
       ('Type2', 0.01, 3276.00),
       ('Type3', 0.01, 3276.00)

DECLARE @CurrentId INT, 
        @Total INT,
        @PreviousCalc MONEY

SET @CurrentId = 1
SELECT @Total = MAX(Id) FROM @Temp

WHILE (@CurrentId <= @Total)
BEGIN
    SELECT @PreviousCalc = Rate * TotalCost
    FROM @Temp
    WHERE Id = @CurrentId - 1

    UPDATE @Temp
    SET TotalCost = TotalCost + ISNULL(@PreviousCalc, 0)
    WHERE Id = @CurrentId

    SET @CurrentId = @CurrentId + 1
END

SELECT * FROM @Temp