本文介绍了在oracle查询中计算运行余额的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的数据
id cp_id amount_a amount_b
CCP1 TTP01 10.000.000 2.000.000
CCP1 TTP02 10.000.000 3.000.000
CCP1 TTP03 10.000.000 1.000.000
CCP1 TTP04 10.000.000 500.000
CCP2 TTP05 5.000.000 1.000.000
CCP2 TTP06 5.000.000 2.000.000
CCP3 TTP07 1.000.000 500.000
我希望结果数据添加如下所示的running_balance列
I want the the result data add one column of running_balance like this below
id amount_a amount_b running_balance
CCP1 10.000.000 2.000.000 8.000.000
CCP1 10.000.000 3.000.000 5.000.000
CCP1 10.000.000 1.000.000 4.000.000
CCP1 10.000.000 500.000 3.500.000
CCP2 5.000.000 1.000.000 4.000.000
CCP2 5.000.000 2.000.000 2.000.000
CCP3 1.000.000 500.000 500.000
我已经做了这样的查询
/* Formatted on 1/26/2017 2:50:06 PM (QP5 v5.115.810.9015) */
SELECT B.NO_KLAIM AS id,
a.amount AS amount_a,
B.AMOUNT AS amount_b,
SUM (A.AMOUNT) OVER (ORDER BY B.AMOUNT ROWS UNBOUNDED PRECEDING)
AS running_balance
FROM TRX_TITIPAN A
JOIN
TRX_KLAIM_TITIPAN B
ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
GROUP BY B.NO_KLAIM, B.AMOUNT, a.amount
但结果未计算出amount_a,仅是total_b的总计.
but the result not calculated amount_a, just running total for amount_b.
**已更新:我已经对查询进行了更新.
**Updated:I make already update on my query.
/* Formatted on 1/26/2017 2:50:06 PM (QP5 v5.115.810.9015) */
SELECT B.NO_KLAIM AS id,
a.amount AS amount_a,
B.AMOUNT AS amount_b,
NVL (TITIP.AMOUNT, 0)
- SUM (NVL (KLAIM.AMOUNT, 0))
OVER (PARTITION BY TITIP.AMOUNT
ORDER BY TITIP.NO_RESI_TITIPAN,
KLAIM.NO_KLAIM,
TITIP.AMOUNT,
KLAIM.AMOUNT asc
ROWS UNBOUNDED PRECEDING) as running_balance
FROM TRX_TITIPAN A
JOIN
TRX_KLAIM_TITIPAN B
ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
GROUP BY B.NO_KLAIM, B.AMOUNT, a.amount
使用带有特定ID的 WHERE id = .... 条件时,运行余额运行正常.当我删除 WHERE id = .... 条件时,这是错误的.
the running balance is working right when using WHERE id= .... condition, with specific id. While i delete WHERE id= .... condition it's going wrong.
推荐答案
我认为您正在这样做:
SELECT B.NO_KLAIM AS id,
a.amount AS amount_a,
B.AMOUNT AS amount_b,
a.amount - SUM (B.AMOUNT)
OVER (partition by b.no_klaim ORDER BY B.cp_id ROWS UNBOUNDED PRECEDING)
AS running_balance
FROM TRX_TITIPAN A
JOIN
TRX_KLAIM_TITIPAN B
ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
这篇关于在oracle查询中计算运行余额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!