select Id,SUM(case type when 1 then Money else -Money end) as [Sum] from 表 group by Id
declare @t table(Id varchar(6),Type int,Money int) insert into @t select 'A',1,5 insert into @t select 'A',2,2 insert into @t select 'B',1,4 insert into @t select 'B',2,4select Id, SUM(case type when 1 then Money else -Money end) as [Sum] from @t group by Id/* Id Sum ------ ----------- A 3 B 0 */
select a.id,sum=isnull(s1,0)-isnull(s2,0) from ((select id, sum(Money) s1 from @t where type=1 group by id) a FUll JOIN ( select id, sum(Money) s2 from @t where type=2 group by id) b on a.id=b.id )
select Id,sum(case type when 1 then Money else -Money end) as [Sum] from tablename group by Id
insert into @t select 'A',1,5
insert into @t select 'A',2,2
insert into @t select 'B',1,4
insert into @t select 'B',2,4select
Id,
SUM(case type when 1 then Money else -Money end) as [Sum]
from
@t
group by
Id/*
Id Sum
------ -----------
A 3
B 0
*/
from tablename group by Id