我有以下表格
QuotationId QuotationDetailId DriverId RangeFrom RangeTo FixedAmount UnitAmount
-------------------------------------------------------------------------------------------
10579 7 1 1 1 1154.00 0.00
10579 7 2 2 2 1731.00 0.00
10579 11 1 0 10 0.00 88.53
10579 11 2 11 24 885.30 100.50
10579 11 3 25 34 2292.30 88.53
我需要用以下逻辑在SQL Server中编写一个查询,
- 分组为QuotationId + QuotationDetailId。
对于每一个block,我需要从第二行开始对前一行的值进行求和对于fixed
Amount + UnitAmount * RangeFrom + FixedAmount of the current row
在这个例子中,输出结果应该是
QuotationId QuotationDetailId DriverId RangeFrom RangeTo FixedAmount UnitAmount
10579 7 1 1 1 1154.00 0.00
10579 7 2 2 2 2885.00 0.00
10579 11 1 0 10 0.00 88.53
10579 11 2 11 24 1770.60 100.50
10579 11 3 25 34 7174.90 88.53
我已经尝试了几个查询,但没有成功,有人能建议我一个方法吗?
致以最亲切的问候法布里奇奥
在SQL Server 2012+中,您可以执行累积求和。我不确定你想要的逻辑是什么,但考虑到数据集,这似乎是合理的:
select t.*,
sum(FixedAmount*UnitAmount) over (partition by QuotationId, QuotationDetailId
order by DriverId
) as running_sum
from t;
您可以使用子查询,您的" amount "列将出现在列列表中,作为括号中的查询,
SELECT ...fields...,
(SELECT SUM(A.unitAmount * A.RangeFrom + A.fixedAmount)
From YourTable A
WHERE A.QuotationId = B.QuotationId
AND A.QuotationDetailId = B.QuotationDetailId
AND A.DriverId <= B.DriverId) AS Amount
From YourTable B