请教sql 提取上一条记录数据对比的写法?
有如下表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
[解决办法]
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)*/