--       新库存  旧库存  变更库存
--公式：NewQty= OldQty + AddedQty  即可： 100+（-1）=99

select Id,skuId,StockId ,OldQty, NewQty, AddedQty,OperateTime from V_Wms_StockLog

--     1    1   , 2     , 100  ,  99   ,  -1    , '2012-5-25'
--     2    1   , 2     , 99   ,  98   ,  -1    , '2012-5-26'
--     3    1   , 2     , 98   ,  97   ,  -1    , '2012-5-27'
--     4    1   , 2     , 97   ,  98   ,   1    , '2012-5-28'
--     5    1   , 2     , 98   ,  100  ,   2    , '2012-5-29'



--求   skuId,StockId,OldQty

--提示：现在用聚合函数sum(AddedQty) ,在用新库存 -  变更库存数量（AddedQty） 

--结果是：skuId   StockId  OldQty

--         1        2      100


我写的--

select LocStock.SkuId,LocStock.StockId,isnull(sum(LocStock.TotalQty-AddedQty),0)as InitStockQty from V_Wms_LocStock  LocStock 
left join (
select skuId,StockId,isnull(sum(stockLog.AddedQty),0) as AddedQty from  V_Wms_StockLog stockLog

group by skuId,StockId
) Wms_stockLog on LocStock.SkuId =LocStock.StockId

group by LocStock.SkuId,LocStock.StockId

--结果：全部是0 这个是错误的  

--SkuId       StockId     InitStockQty
------------- ----------- --------------------
--1            2             0

select  SkuId,StockId,isnull(sum(NewQty),0)-isnull(sum(AddedQty),0) as InitStockQty from V_Wms_LocStock  group by   SkuId,StockId

------解决方案--------------------
楼主需求是什么意思?

------解决方案--------------------
sum(LocStock.TotalQty-AddedQty
这个TotalQty哪里来的？

------解决方案--------------------

提问也是一种技巧

------解决方案--------------------
什么啊

------解决方案--------------------
SQL code

declare @V_Wms_StockLog table 
(Id int,skuId int,StockId int,OldQty int,NewQty int,AddedQty int,OperateTime datetime)
insert into @V_Wms_StockLog
select 1,1,2,100,99,-1,'2012-5-25' union all
select 2,1,2,99,98,-1,'2012-5-26' union all
select 3,1,2,98,97,-1,'2012-5-27' union all
select 4,1,2,97,98,1,'2012-5-28' union all
select 5,1,2,98,100,2,'2012-5-29'

select top 1 skuId,StockId,
sum(OldQty) as OldQty1,sum(NewQty-AddedQty) as OldQty2 
from @V_Wms_StockLog group by skuId,StockId,ID
/*
skuId       StockId     OldQty1     OldQty2
----------- ----------- ----------- -----------
1           2           100         100
*/