我有一个表testsql数据如下：
------------------------------
ID num type
1 80 进货
2 10 出货
3 40 进货
1 5 进货
2 140 出货
3 15 出货
------------------------------
我现在想得到如下的表数据：
ID 进货 出货 库存增加
1 85 0 85
2 0 150 -150
3 40 15 35
-------------------------------
create table testsql
(
id int,
num int,
type varchar(10)
)
go
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,'出货'
go
第一种：
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,进货,出货,(进货-出货)as 库存增加 from
(select id,sum(case when type='进货'then num else ''end) 进货,
sum(case when type='出货'then num else ''end) 出货
from dbo.testsql group by id)aa
