3,490
社区成员
发帖
与我相关
我的任务
分享
UPDATE B SET JE = JE + (
SELECT MAX(A.JE) - SUM(InnerB.JE)
FROM A, B InnerB
WHERE A.CBZX = InnerB.LY AND InnerB.LY = B.LY
GROUP BY LY)
WHERE (LY, JE, CB || '1') IN (SELECT LY,
MAX(JE) OVER (PARTITION BY LY),
CB || ROW_NUMBER() OVER (PARTITION BY LY ORDER BY JE DESC)
FROM B);
UPDATE B SET JE = JE + (
SELECT MAX(A.JE) - SUM(InnerB.JE)
FROM A, B InnerB
WHERE A.CBZX = InnerB.LY AND InnerB.LY = B.LY
GROUP BY LY)
WHERE (LY, JE) IN (SELECT LY, MAX(JE) OVER (PARTITION BY LY) FROM B);
SQL> SELECT * FROM B;
C L JE
- - ---------
c a 150
d a 25
e a 24.99999
f b 100.99999
g b 100.99999
h b 96.99999
已选择6行。
SQL> UPDATE B SET B.JE=B.JE+(
2 SELECT A.JE-SUM(BB.JE) FROM A,B BB WHERE A.CBZX=BB.LY AND A.CBZX=B.LY GROUP BY A.JE
3 )
4 WHERE (CB,LY) IN (
5 SELECT MAX(CB),LY FROM B WHERE (LY,JE) IN (SELECT LY,MAX(JE) FROM B GROUP BY LY)
6 GROUP BY LY);
已更新2行。
SQL> SELECT * FROM B;
C L JE
- - ---------
c a 150.00001
d a 25
e a 24.99999
f b 100.99999
g b 102.00002
h b 96.99999
已选择6行。
SQL>
select b0.ly, b0.total, b0.je, a.je,
( select cb from b where je=b0.je and ly=b0.ly and rownum=1 )cb
from
( select ly, sum(je) total, max(je) je from b group by ly )b0, a
where b0.ly=a.cbzx