用oracle写一个加减算法

[jin_cto]

在一个表格中有两列数据，第二行的SUM_LJ等于第一行的SUM_LJ加上第二行的QTY,以此类推，找出不正确的SUM_LJ所在的哪一行

[卖水果的net]

可以使用下面的语法结构， sum（A，s_sum） over（order by flowno） 手机不方便。

[jin_cto]

SELECT * FROM (select POSNO, FLOWNO, SUM(QTY), SUM_LJ, sum(sum(QTY)) over(order by FLOWNO rows between unbounded preceding and current row) cumulative_amount FROM HWH GROUP BY FLOWNO,POSNO,SUM_LJ) WHERE SUM_LJ<>cumulative_amount

[zbdzjx]

with table1 as
(
select '00001' posno, '201310310001' flowno, 1 qty, 1 sum_lj union all
select '00001' posno, '201310310002' flowno, -1 qty, 0 sum_lj union all
select '00001' posno, '201310310003' flowno, 1 qty, 1 sum_lj union all
select '00001' posno, '201310310004' flowno, -1 qty, 1 sum_lj union all
select '00001' posno, '201310310005' flowno, 1 qty, 1 sum_lj union all
select '00001' posno, '201310310006' flowno, -1 qty, 3 sum_lj 
)
select posno, flowno, qty, sum_lj
, (select sum(qty) from table1 b where b.flowno<=a.flowno) sum_new
from table1 a

[jin_cto]

我使用了sum（A，s_sum） over（order by flowno），但是后面怎么做对比出不正确的行？

[o_range]

引用 3 楼 jin_cto 的回复:

我使用了sum（A，s_sum） over（order by flowno），但是后面怎么做对比出不正确的行？

sum（A，s_sum） over（order by flowno） as SUM_CO 你再嵌套一层 WHERE SUM_CO<>SUM_LJ

[陈灬风]

with tmp as (select '00001' posno, '201310310001' flowno, 1 qty, 1 sum_lj from dual union all select '00001' posno, '201310310002' flowno, -1 qty, 0 sum_lj from dual union all select '00001' posno, '201310310003' flowno, 1 qty, 1 sum_lj from dual union all select '00001' posno, '201310310004' flowno, -1 qty, 1 sum_lj from dual union all select '00001' posno, '201310310005' flowno, 1 qty, 1 sum_lj from dual union all select '00001' posno, '201310310006' flowno, -1 qty, 3 sum_lj from dual), tmp1 as (select tmp.*, lag （sum_lj, 1, 0）over(partition by posno order by flowno) su from tmp) select tmp1.* from tmp1 where tmp1.sum_lj != (tmp1.qty + tmp1.su)

[jin_cto]

做出来了，语句是这样： SELECT H.POSNO, A.*,H.SUM_LJ FROM HWH H,（select FLOWNO, SUM(QTY), sum(sum(QTY)) over(order by FLOWNO rows between unbounded preceding and current row) cumulative_amount FROM HWH GROUP BY FLOWNO）A WHERE H.FLOWNO=A.FLOWNO AND H.SUM_LJ<>A.cumulative_amount; 但是该怎么在优化点，速度更优化