有一表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
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 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);也会提示不能分组。
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;
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
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
)
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;
(
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
(
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
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;
set EndQty=(select sum(InQty-OutQty) from tab1 t2 where t2.SID<=t1.SID)
谢谢noteasytoregister,你的脚本是对的。这个求余额的sql,
1 应该是单表更新,
2 而不是普通的多表更新,
3 oracle里单表更新,不能加from子句,而sqlserver中允许。
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;
--又忘记加括号了,日。
select (select sum(ta.inqty-ta.outqty) from tab1 ta where ta.sid<=t.sid)
from tab1 t where tab.sid=t.sid)