所以我有一个发货列表,我有订单总额和每个单独发货的总额,但我很难想出代码来为累计发货创建一个额外的列,其中包括当前发货以及该订单以前的所有发货。以下是我迄今为止的一个结果:
订单号 | 发货总数 | 累计发货次数排名 | |||
---|---|---|---|---|---|
22396 | 2022-04-04 | >td>6399642983 | 639966 | 3 | |
22396 | 2022-03-31 | >639964 | 5626 | 639966 | 2 |
22396 | 2022-02-24 | >639964 | 631355 | 6399661
您似乎已经走到了一半,只是缺少了函数累积和的排序标准,例如
select *,
Sum(ShipmentTotal)
over(partition by OrderNo
order by ShipDate rows between unbounded preceding and current row)
from Shipments;
我没有费心构建CTE,但假设你有一些PK,你可以自加入:
SELECT s.ShipmentsID, SUM(cumulative.ShipmentTotal) AS Sum
FROM Shipments s
LEFT JOIN Shipments cumulative ON cumulative.ShipDate <= s.ShipDate
GROUP BY s.ShipmentsID
ShipmentsID | 总和 |
---|---|
1 | 631355.52 |
2 | 636982.29 |
3 | 639966.04 |