如下表:
table1:name inorout num
001 in 100
001 out 50
002 in 200
003 in 10
003 out 8求同一name值的in与out之差,并存放在result字段之中
没有out的行不做改变。网上搜索的都是求两“列”差值,好像没有我这样求两行的,帮忙看看
谢谢
table1:name inorout num
001 in 100
001 out 50
002 in 200
003 in 10
003 out 8求同一name值的in与out之差,并存放在result字段之中
没有out的行不做改变。网上搜索的都是求两“列”差值,好像没有我这样求两行的,帮忙看看
谢谢
declare @t table (name varchar(20),inorout varchar(20) , num int)
insert into @t select '001', 'in' , 100
insert into @t select '001', 'out', 50
insert into @t select '002', 'in' , 200
insert into @t select '003', 'in' , 10
insert into @t select '003', 'out' , 8 select name, sum(Case when inorout='in' then num else -num end) from @t group by name
/*
001 50
002 200
003 2
*/
declare @t table (name varchar(20),inorout varchar(20) , num int)
insert into @t select '001', 'in' , 100
insert into @t select '001', 'out', 500
insert into @t select '002', 'in' , 200
insert into @t select '003', 'in' , 10
insert into @t select '003', 'out' , 8 select t.* ,case when charindex('-',t.sum_)>0 then 'out' else 'in' end inorout
from (select name, sum(Case when inorout='in' then num else -num end) sum_ from @t group by name)t
/*
001 -400 out
002 200 in
003 2 in
*/