-----比较卡的4个查询一下4个 请大家看看什么问题 造成的
----- 5832条数据 本地一共 要用28秒 服务器上要60秒以上 太慢了
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量
---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量
from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
要求改成把中间4个select 子句改成 把子查询表作为一个连接,那只要扫一次就行了
参考帖子:http://topic.csdn.net/u/20120703/11/e278f81f-58ce-4059-bba0-20b50b2e21fa.html