有如下表t1,结构如下,其中字段 sz-status 当数值为:s 表示收入 z: 表示支出
id price sz-status
1 12 s
2 3 z
3 2.5 z
4 4.6 z
5 10 s
希望得到如下结果
id price sz-status 结余
1 12 s 12
2 3 z 9
3 2.5 z 6.5
4 4.6 z 1.9
5 10 s 11.9
------解决方案--------------------
- SQL code
create table t1(id int, price decimal(5,1), [sz-status] char(1))insert into t1select 1, 12, 's' union all select 2, 3, 'z' union all select 3, 2.5, 'z' union all select 4, 4.6, 'z' union all select 5, 10, 's'select a.id,a.price,a.[sz-status],(select sum(case b.[sz-status] when 's' then b.price when 'z' then -1*b.price end) from t1 b where b.id<=a.id) '结余'from t1 a/*id price sz-status 结余----------- ------------ --------- ---------1 12.0 s 12.02 3.0 z 9.03 2.5 z 6.54 4.6 z 1.95 10.0 s 11.9(5 row(s) affected)*/