现在有一表A
table AstoreId status itemId count price
001 入库 0001 5 350
001 入库 0002 15 200
001 售出 0001 10 700
001 售出 0002 10 150
002 入库 0001 10 700
002 售出 0002 15 200
002 售出 0001 5 400现在要这样的效果
storeId itemId count countIn countOut price priceIn priceOut
001 0001 15 5 10 1050 350 700
001 0002 25 15 10 350 200 100
002 0001 15 10 5 1100 700 400
002 0002 15 0 15 200 0 200问一下这句S Q L语句怎么写?
高手教我!~~~
table AstoreId status itemId count price
001 入库 0001 5 350
001 入库 0002 15 200
001 售出 0001 10 700
001 售出 0002 10 150
002 入库 0001 10 700
002 售出 0002 15 200
002 售出 0001 5 400现在要这样的效果
storeId itemId count countIn countOut price priceIn priceOut
001 0001 15 5 10 1050 350 700
001 0002 25 15 10 350 200 100
002 0001 15 10 5 1100 700 400
002 0002 15 0 15 200 0 200问一下这句S Q L语句怎么写?
高手教我!~~~
storeId,
itemId,
sum([count]) as [count],
sum(case when status='入库' then [count] else 0 end) as CountIn,
sum(case when status<>'入库' then [count] else 0 end) as CountOut,
sum(price) as price,
sum(case when status<>'售出' then [price] else 0 end) as priceIn,
sum(case when status='售出' then [price] else 0 end) as priceOut
from A
group by storeId,itemId
order by storeId,itemId
sum(case status when '入库' then [count] else 0 end) countIn,
sum(case status when '售出' then [count] else 0 end) countout,
sum(price) price,
sum(case status when '入库' then price else 0 end) priceIn,
sum(case status when '售出' then price else 0 end) priceout
from A
group by storeId,itemId
order by storeId,itemId
insert into a values('001','入库','0001',5 , 350 )
insert into a values('001','入库','0002',15 , 200 )
insert into a values('001','售出','0001',10 , 700 )
insert into a values('001','售出','0002',10 , 150 )
insert into a values('002','入库','0001',10 , 700 )
insert into a values('002','售出','0002',15 , 200 )
insert into a values('002','售出','0001',5 , 400 )
go select storeId,itemId,sum([count]) [count],
sum(case status when '入库 ' then [count] else 0 end) countIn,
sum(case status when '售出 ' then [count] else 0 end) countout,
sum(price) price,
sum(case status when '入库 ' then price else 0 end) priceIn,
sum(case status when '售出 ' then price else 0 end) priceout
from A
group by storeId,itemId
order by storeId,itemId drop table a/*
storeId itemId count countIn countout price priceIn priceout
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
001 0001 15 5 10 1050 350 700
001 0002 25 15 10 350 200 150
002 0001 15 10 5 1100 700 400
002 0002 15 0 15 200 0 200(所影响的行数为 4 行)
*/