我有一个表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
-------------------------------
------------------------------
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,'出货'
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
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
(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