SQL 修改累计值|极客教程

SQL 修改累计值，你想依据另一列的值来修改累计值。试想这样的场景：你希望显示一个信用卡账户的交易历史，并显示每一笔交易完成后的余额。本实例将会用到如下所示的视图 V。

SQL 修改累计值问题描述

你想依据另一列的值来修改累计值。试想这样的场景：你希望显示一个信用卡账户的交易历史，并显示每一笔交易完成后的余额。本实例将会用到如下所示的视图 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 TRX
-- ---------- ---
 1        100  PR
 2        100  PR
 3         50  PY
 4        100  PR
 5        200  PY
 6         50  PY

ID 列能唯一地标示每一笔交易。AMT 列代表每一笔交易涉及的金额（要么是还款，要么是购物）。TRX 列定义交易的类型：还款是 PY，购物是 PR。如果 TRX 的值是 PY，你希望能从累计值里减去当前的 AMT 值。如果 TRX 的值是 PR，你希望累计值加上当前的 AMT 值。最终，你想得到如下所示的结果集。

TRX_TYPE        AMT    BALANCE
-------- ---------- ----------
PURCHASE        100        100
PURCHASE        100        200
PAYMENT          50        150
PURCHASE        100        250
PAYMENT         200         50
PAYMENT          50          0

SQL 修改累计值解决方案

DB2 和Oracle
使用窗口函数 SUM OVER 进行累计求和，并使用 CASE 表达式来决定交易的类型。

 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
使用标量子查询进行累计求和，并使用 CASE 表达式来决定交易的类型。

 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

SQL 修改累计值扩展知识

CASE 表达式用于决定是把当前的 AMT 值加到累计值中，还是从累计值中减去当前的 AMT 值。如果交易类型是还款，AMT 值会被变为负数，因而相应的累计值会减少。CASE 表达式的结果如下所示。

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

依据交易类型的评估结果，AMT 值会被加入累计值，或者从累计值中减去。关于如何使用窗口函数 SUM OVER 或者标量子查询进行累计求和，请参考 7.6 节。