select id=identity(int,1,1),
[年月日],[摘要],
[借方金额]=case when [方向]='借' then [金 额] else 0 end,
[贷方金额]=case when [方向]='贷' then [金 额] else 0 end,
into #t
from yourtableselect a.[年月日],a.[摘要],a.[借方金额],a.[贷方金额],
[余额]=(a.[借方金额]-a.[贷方金额])-(b.[借方金额]-b.[贷方金额])
from #t a,#t b
where a.id=b.id+1
[年月日],[摘要],
[借方金额]=case when [方向]='借' then [金 额] else 0 end,
[贷方金额]=case when [方向]='贷' then [金 额] else 0 end,
into #t
from yourtableselect a.[年月日],a.[摘要],a.[借方金额],a.[贷方金额],
[余额]=(a.[借方金额]-a.[贷方金额])-(b.[借方金额]-b.[贷方金额])
from #t a,#t b
where a.id=b.id+1
id nyr zy direction amount
1 NULL NULL j 100
2 NULL NULL d 200
3 NULL NULL j 500
select id,case when a.direct='j' then a.amount end as jj,
case when a.direct='d' then a.amount end as dd ,
(select sum(case when b.direct='j' then amount else -1*amount end) from test1 b
where b.id <=a.id) as bb
from test1 aid jj dd bb
1 100 NULL 100
2 NULL 200 -100
3 500 NULL 400