现在有一表A
table A
storeId 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语句怎么写?
高手教我!~~~
------解决方案--------------------
- SQL code
select 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 Agroup by storeId,itemIdorder by storeId,itemId
------解决方案--------------------
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