select ID,
Sum(case when type='进货' then num else 0) as 进货,
Sum(case when type='出货' then num else 0) as 出货,
Sum(case when type='进货' then num else -1 * num) as 库存增加,
from testsql
group by ID
Sum(case when type='进货' then num else 0) as 进货,
Sum(case when type='出货' then num else 0) as 出货,
Sum(case when type='进货' then num else -1 * num) as 库存增加,
from testsql
group by ID
(
id int,
num int,
type varchar(10)
)
insert testsql
select 1,80,'进货' union
select 2,10,'出货' union
select 3,40,'进货' union
select 1,5,'进货' union
select 2,140,'出货' union
select 3,15,'出货'select Id,
sum(case when type='进货' then num else 0 end) as 进货,
sum(case when type='出货' then num else 0 end) as 出货,
(sum(case when type='进货' then num else 0 end)-sum(case when type='出货' then num else 0 end)) as 库存增加
from testsql
group by Id
select ID,
Sum(case when type='进货' then num else 0 end) as 进货,
Sum(case when type='出货' then num else 0 end) as 出货,
Sum(case when type='进货' then num else -1 * num end) as 库存增加,
from testsql
group by ID
sum(case when type='进货' then num else 0 end )as '进货' ,
sum(case when type='出货' then num else 0 end )as '出货' ,
sum(case when type='进货' then num else -1*num end ) as '库存增加'
From testsql
Group By id ,type
Order By id
insert @tb1
select 1, 80, '进货' union all
select 2, 10, '出货' union all
select 3, 40, '进货' union all
select 1, 5, '进货' union all
select 2, 140, '出货' union all
select 3, 15, '出货'
select id,
Sum(case when type='进货' then num else 0 end) as 进货,
Sum(case when type='出货' then num else 0 end) as 出货,
sum(case type when '进货' then num else (-num) end) as 库存
from @tb1
group by id
/*
测试结果
id 进货 出货 库存
----------- ----------- ----------- -----------
1 85 0 85
2 0 150 -150
3 40 15 25
*/
(select id,sum(case when type='进货'then num else ''end) 进货,
sum(case when type='出货'then num else ''end) 出货
from aa group by id)a