表结构如下:
goodsname goodscount goodstype
---------- ----------- -----------
book 23 0
book 1 1
book1 100 0
book1 10 1
目的:用货物类型为0的数量 - 货物类型为1的数量
预期结果:
goodsname goodscount
---------- -----------
book 22
book1 90 谢谢大家了。解决问题就揭贴。
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 goodsnamedrop table #t
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
--按goodsname分组,用case when处理
select
goodsname,
sum(case when goodstype=1 then goodscount*-1 else goodscount end) as goodscount
from #t
group by goodsnamedrop table #t
---------- ----------- -----------
book 23 0
book 1 1
book1 100 0
book1 10 1select 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
--用一下SQLselect
goodsname,
sum(case when goodstype=1 then goodscount*-1 else goodscount end) as goodscount
from 你的表名
group by 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
drop table tb
gocreate table tb(goodsname varchar(10),goodscount int,goodstype int)
insert into tb(goodsname,goodscount,goodstype) values('book' , 23 , 0)
insert into tb(goodsname,goodscount,goodstype) values('book' , 1 , 1)
insert into tb(goodsname,goodscount,goodstype) values('book1', 100, 0)
insert into tb(goodsname,goodscount,goodstype) values('book1', 10 , 1)goselect isnull(m.goodsname,n.goodsname) goodsname , isnull(m.goodscount,0) - isnull(n.goodscount ,0) goodscount 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.goodsnamedrop table tb/*
goodsname goodscount
---------- -----------
book 22
book1 90(所影响的行数为 2 行)
*/
你太厉害了,你是怎么做到的这个程度的,可以交流下学习经验么。谢谢