求一个SQL语句，谢谢大家了。解决问题就揭贴。
表结构如下：
goodsname       goodscount       goodstype        
----------   -----------   -----------  
book                   23                           0
book                   1                             1
book1                 100                         0
book1                 10                           1
 
目的：用货物类型为0的数量   -   货物类型为1的数量
预期结果：
goodsname       goodscount    
----------   -----------  
book                   22                          
book1                 90    

谢谢大家了。解决问题就揭贴。

------解决方案--------------------

create table #T(goodsname varchar(100), goodscount int, goodstype int)

insert into #t


select 'book ',23, 0 union all
select 'book ',1, 1 union all
select 'book1 ',100,0 union all
select 'book1 ',10, 1


select goodsname,sum(case when goodstype=1 then goodscount*-1 else goodscount end) as goodscount
from #t
group by goodsname

drop table #t


------解决方案--------------------
goodsname goodscount goodstype
---------- ----------- -----------
book 23 0
book 1 1
book1 100 0
book1 10 1

select isnull(m.goodsname,n.goodsname) goodsname , isnull(m.goodscount,0) - isnull(n.goodscount goodscount,0) from
(select goodsname,sum(goodscount) goodscount from tb where goodstype = 0 group by goodsname) m
full join
(select goodsname,sum(goodscount) goodscount from tb where goodstype = 1 group by goodsname) n
on m.goodsname = n.goodsname


------解决方案--------------------

create table temptb
(
goodsname varchar(10),
goodscount int,
goodstype int
)

insert into temptb select 'book ',23, 0
union all select 'book ',1,1
union all select 'book1 ',100,0
union all select 'book1 ',10,1


select goodsname,sum(case when goodstype=1 then -goodscount end)+sum(case when goodstype=0 then goodscount end ) goodscount from temptb group by goodsname