每行 3 列总和结果在最后一列显示为运行总计，然后从第 2 行>第一行运行总计显示在第一列中，分组依据

Question

每一行除第一行外，下一行的值为set OP值

My Table Data

<表类> Id WarehouseId ProductId OP RE 总 tbody><<tr>1100100001000102100100000055410010000015015510110001150015610110001005581011000101501591011000225002510101100020010101110110002015015

您可以像下面这样使用递归子查询分解。您需要对数据进行排序，因为数据中的id之间存在一些差距。不允许列名= IS，您应该使用"IS">

with t1 ( ID, WAREHOUSEID, PRODUCTID, OP, RE, "IS", RNB, rnb_per_wh_prod) as (
select ID, WAREHOUSEID, PRODUCTID, OP, RE, "IS"
, row_number()over(order by WAREHOUSEID, PRODUCTID, ID) rnb
, row_number()over(partition by WAREHOUSEID, PRODUCTID order by ID) rnb_per_wh_prod
from Your_table t
), cte (ID, WAREHOUSEID, PRODUCTID, OP, RE, "IS", Total, RNB) as (
select ID, WAREHOUSEID, PRODUCTID, OP, RE, "IS", OP + RE - "IS", RNB
from t1
where rnb_per_wh_prod = 1
union all
select  t1.ID
, t1.WAREHOUSEID
, t1.PRODUCTID
, case when t1.op = 0 then c.OP + c.RE - c."IS" else t1.OP end as OP
, t1.RE
, t1."IS"
, case when t1.op = 0 then c.OP + c.RE - c."IS" else t1.OP end + t1.RE - t1."IS" total
, t1.RNB
from cte c
join t1 on (t1.RNB = c.RNB + 1 
and c.WAREHOUSEID = t1.WAREHOUSEID 
and c.PRODUCTID = t1.PRODUCTID)
)
select ID, WAREHOUSEID, PRODUCTID, OP, RE, "IS", TOTAL
from cte
order by id
;

如果我理解正确的话，"每3行"是你数据上的巧合;实际上，无论每个不同的组合有多少行，必须对每个仓库中的每个产品单独进行计算。然后是"id"列实际上是某种时间戳——按"id"排序。

如果是这样，您可以使用解析sum()在单个查询中完成所有操作。我没有为测试创建一个表，而是将所有示例数据包含在查询本身顶部的WITH子句中;您可以删除WITH子句，并使用实际的表和列名。我还将列名称is更改为is_，因为是是一个保留关键字，它不能是列名。

还请注意，我完全忽略了您现有的total列(我甚至没有将其包括在示例数据中);我假设它不存在于您的实际数据中，相反，它是您尝试解决方案的一部分。你不需要它——不像你的问题那样需要它。

with
sample_data (id, warehouseid, productid, op, re, is_) as (
select  1, 100, 10000, 10,  0,  0 from dual union all
select  2, 100, 10000,  0,  0,  5 from dual union all
select  4, 100, 10000,  0, 15,  0 from dual union all
select  5, 101, 10001, 15,  0,  0 from dual union all
select  6, 101, 10001,  0,  0,  5 from dual union all
select  8, 101, 10001,  0, 15,  0 from dual union all
select  9, 101, 10002, 25,  0,  0 from dual union all
select 10, 101, 10002,  0,  0, 10 from dual union all
select 11, 101, 10002,  0, 15,  0 from dual
)
select id, warehouseid, productid,
nvl(sum(op + re - is_) over (partition by warehouseid, productid
order by id rows between unbounded preceding and 1 preceding),
op) as op,
re, is_,
sum(op + re - is_) over 
(partition by warehouseid, productid order by id) as total
from   sample_data
;
ID WAREHOUSEID  PRODUCTID         OP         RE        IS_      TOTAL
------ ----------- ---------- ---------- ---------- ---------- ----------
1         100      10000         10          0          0         10
2         100      10000         10          0          5          5
4         100      10000          5         15          0         20
5         101      10001         15          0          0         15
6         101      10001         15          0          5         10
8         101      10001         10         15          0         25
9         101      10002         25          0          0         25
10         101      10002         25          0         10         15
11         101      10002         15         15          0         30