Oracle怎么写一个求余额的sql语句。

[披着虎皮 的石头]

有一表tab1，表里有几个字段和记录：
SID InQty OutQty EndQty
001 2.5 1.5
002 3.5 2.5
003 4.5 1.5

怎么写sql语句，根据 上次余额 +InQty -OutQty =EndQty,更新EndQty值，结果为：
SID InQty OutQty EndQty
001 2.5 1.5 1
002 3.5 2.5 2
003 4.5 1.5 5

以前的sqlserver中，如下写法是对的，但到oracle中就报错:
update tab1
set EndQty = select Sum(InQty ) -Sum(OutQty)
from #tab1 where SID <=t.SID)
from #tab1 t

[中国风]

update t2
set EndQty=t1.TotalQty
from 
(Select *,(select SUM(InQty-OutQty) from tab1 where SID<=a.SID) as TotalQty from tab1 as a)t1
inner join tab1 as t2 on t1.SID=t2.SID

[chenro]

update tab1
set endqty = (select t0.t_qty
from
(select *, sum(inqty - outqty) over(partition by sid) as t_qty
from tab1
) t0
where tab1.sid = t0.sid
)

[BenChiM888]

[Quote=引用 3 楼 zhuchuankun 的回复:]

好复杂啊，运行报ORA-00969：缺少ON关键字。
[/Quote]


ON TAB1.SID = B.SID 少个括号 sorry

ON (TAB1.SID = B.SID)

[xiaobn_cn]

可以用分析函数来解决

with T as (
select '001' sid, 2.5 inqty,1.5 outqty from dual union
select '002',3.5,2.5 from dual union
select '003', 4.5,1.5 from dual)
-- 上面的是测试模拟数据，不需要引用
SELECT SID,OUTQTY,OUTQTY,SUM(INQTY - OUTQTY) OVER(ORDER BY SID) ENDQTY FROM T;

[披着虎皮 的石头]

create table TAB1
(
SID VARCHAR2(50),
Inqty NUMBER(18,6),
OutQty NUMBER(18,6),
EndQty NUMBER(18,6)
);

insert into tab1 (SID, Inqty, OutQty)
values ('1', 1, 1);

insert into tab1 (SID, Inqty, OutQty)
values ('2', 2, 1);

insert into tab1 (SID, Inqty, OutQty)
values ('3', 3, 1);

--这里写更新EndQty值

select SID, Inqty, OutQty, EndQty from tab1
order by SID;
--结果为（上次余额+inQty -outQty =EndQty）:
SID INQTY OutQty EndQty
1 1 1 0
2 2 1 1
3 3 1 3

[披着虎皮 的石头]

各位大侠，写的sql，都不能通过编译，我重新写了建表和插数据的脚本，大家调试一下，测试一下结果。

[披着虎皮 的石头]

create table TAB1
(
  SID VARCHAR2(50),
  Inqty NUMBER(18,6),
  OutQty NUMBER(18,6),
  EndQty NUMBER(18,6)
);

insert into tab1 (SID, Inqty, OutQty)
values ('1', 1, 1);

insert into tab1 (SID, Inqty, OutQty)
values ('2', 2, 1);

insert into tab1 (SID, Inqty, OutQty)
values ('3', 3, 1);

--这里写更新EndQty值

select SID, Inqty, OutQty, EndQty from tab1
order by SID;
--结果为（上次余额+inQty -outQty =EndQty）:
SID INQTY OutQty EndQty
1 1 1 0
2 2 1 1
3 3 1 3

[BenChiM888]

MERGE INTO TAB1 t1
USING (SELECT TMP.SID, SUM(TAB1.INQTY) - SUM(TAB1.OUTQTY) ENDQTY
         FROM TAB1, TAB1 TMP
        WHERE TAB1.SID <= TMP.SID
        GROUP BY TMP.SID) B
ON t1.SID = B.SID
WHEN MATCHED THEN
  UPDATE SET t1.ENDQTY = B.ENDQTY;

[noteasytoregister]

update tab1 t1
  set EndQty=(select sum(InQty-OutQty) from tab1 t2 where t2.SID<=t1.SID)

[披着虎皮 的石头]

[Quote=引用 11 楼 benchim888 的回复:]

SQL code


MERGE INTO TAB1 t1
USING (SELECT TMP.SID, SUM(TAB1.INQTY) - SUM(TAB1.OUTQTY) ENDQTY
FROM TAB1, TAB1 TMP
WHERE TAB1.SID <= TMP.SID
GROUP BY TMP.SID) B
ON t1.SID = ……
[/Quote]
谢谢noteasytoregister，你的脚本是对的。

这个求余额的sql，
1 应该是单表更新，
2 而不是普通的多表更新，
3 oracle里单表更新，不能加from子句，而sqlserver中允许。

[BenChiM888]

MERGE INTO TAB1 t1
USING (SELECT TMP.SID, SUM(TAB1.INQTY) - SUM(TAB1.OUTQTY) ENDQTY
         FROM TAB1, TAB1 TMP
        WHERE TAB1.SID <= TMP.SID
        GROUP BY TMP.SID) B
ON (t1.SID = B.SID)
WHEN MATCHED THEN
  UPDATE SET t1.ENDQTY = B.ENDQTY;
--又忘记加括号了，日。

[programmerxiaocai]

update tab1 tab set (tab.endqty)=(
select (select sum(ta.inqty-ta.outqty) from tab1 ta where ta.sid<=t.sid)
from tab1 t where tab.sid=t.sid)

[披着虎皮 的石头]

而且写成:
update tab1 t
set EndQty =Sum(tt.InQty) -Sum(tt.OutQty) from a tt where tt.SID <=t.SID)
WHERE EXISTS (SELECT * from a tt where tt.SID <=t.SID);

也会提示不能分组。

[BenChiM888]

MERGE INTO TAB1
USING (SELECT TMP.SID, SUM(TAB1.INQTY) - SUM(TAB1.OUTQTY) ENDQTY
         FROM TAB1, TAB1 TMP
        WHERE T1.SID <= TMP.SID
        GROUP BY TMP.SID) B
ON TAB1.SID = B.SID
WHEN MATCHED THEN
  UPDATE SET TAB1.ENDQTY = B.ENDQTY;

[披着虎皮 的石头]

好复杂啊，运行报ORA-00969：缺少ON关键字。