問題:根據(jù)另一列中的值修改累計和中的值。假設(shè)一個場景，要顯示信用卡賬號的事務(wù)處理歷史以及每次事務(wù)處理洲改累計和中的值。假設(shè)一個場景，要顯示信用卡賬號的事務(wù)處理歷史以及每次事務(wù)處理之后的當(dāng)前余額。在這個例子中，將使用下面給出的視圖V：
create view V (id,amt,trx)
as
select 1, 100, 'PR' from t1 union all
select 2, 100, 'PR' from t1 union all
select 3, 50, 'PY' from t1 union all
select 4, 100, 'PR' from t1 union all
select 5, 200, 'PY' from t1 union all
select 6, 50, 'PY' from t1
select * from V
ID AMT TR
-- ---------- --
1 100 PR
2 100 PR
3 50 PY
4 100 PR
5 200 PY
6 50 PY
ID列唯一標(biāo)識每次事務(wù)處理。AMT列表示每次事務(wù)處理（取款或存款）涉及的金額。TRX列定義了事務(wù)處理的類型；取款是“PY”，存款是“PR”。如果TRX值是PY，則想要從累計和中減去AMT值代表的金額；如果TRX值是PR，則想要給累計和加上AMT值代表的金額。最后應(yīng)該返回如下結(jié)果集：
TRX_TYPE AMT BALANCE
-------- ---------- ----------
PURCHASE 100 100
PURCHASE 100 200
PAYMENT 50 150
PURCHASE 100 250
PAYMENT 200 50
PAYMENT 50 0
解決方案
DB2和Oracle
使用窗口函數(shù)SUM OVER創(chuàng)建累計和，并使用CASE表達(dá)式判斷事務(wù)處理的類型：
1 select case when trx = 'PY'
2 then 'PAYMENT'
3 else 'PURCHASE'
4 end trx_type,
5 amt,
6 sum(
7 case when trx = 'PY'
8 then -amt else amt
9 end
10 ) over (order by id,amt) as balance
11 from V
MySQL、PostgreSQL和SQL Server
使用標(biāo)量子查詢創(chuàng)建累計和，并使用CASE表達(dá)式判斷事務(wù)處理的類型：
1 select case when v1.trx = 'PY'
2 then 'PAYMENT'
3 else 'PURCHASE'
4 end as trx_type,
5 v1.amt,
6 (select sum(
7 case when v2.trx = 'PY'
8 then -v2.amt else v2.amt
9 end
10 )
11 from V v2
12 where v2.id <= v1.id) as balance
13 from V v1
討論
CASE表達(dá)式判斷是該給累計和加上當(dāng)前的AMT值還是從中減去當(dāng)前的AMT值 。如果事務(wù)處理是取款，則把AMT更改為負(fù)值，這樣就減少了累計和。CASE表達(dá)式的結(jié)果如下所示：
select case when trx = 'PY'
then 'PAYMENT'
else 'PURCHASE'
end trx_type,
case when trx = 'PY'
then -amt else amt
end as amt
from V
TRX_TYPE AMT
-------- ---------
PURCHASE 100
PURCHASE 100
PAYMENT -50
PURCHASE 100
PAYMENT -200
PAYMENT -50
在確定了事務(wù)處理類型之后，就可以從累計和中加上或者減去AMT值。有關(guān)窗口函數(shù)SUM OVER或標(biāo)量子查詢?nèi)绾蝿?chuàng)建累計和的說明，請參閱“計算累計和”。