表结构如下：
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