if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[data] numeric(2,1),[data2] sql_variant) insert [tb] select 1,0.5,null union all select 2,1.5,null union all select 3,2.0,null union all select 4,3.5,nullselect a.id,a.data, data2=a.data-isnull(b.data,0) from tb a left join tb b on a.id=b.id+1/** id data data2 ----------- --------------------------------------- --------------------------------------- 1 0.5 0.5 2 1.5 1.0 3 2.0 0.5 4 3.5 1.5(4 行受影响) **/
2楼可用,a是表别名,你把[Table]改为你的表名即可。
go
create table [tb]([id] int,[data] numeric(2,1),[data2] sql_variant)
insert [tb]
select 1,0.5,null union all
select 2,1.5,null union all
select 3,2.0,null union all
select 4,3.5,nullselect a.id,a.data,
data2=a.data-isnull(b.data,0)
from tb a left join tb b on a.id=b.id+1/**
id data data2
----------- --------------------------------------- ---------------------------------------
1 0.5 0.5
2 1.5 1.0
3 2.0 0.5
4 3.5 1.5(4 行受影响)
**/