-----比较卡的4个查询一下4个 请大家看看什么问题 造成的
     ----- 5832条数据 本地一共 要用28秒   服务器上要60秒以上 太慢了
select  t0.SkuId as 产品ID, t0.SkuNo as 规则编号, t0.ProductNo as 商品编号, t0.ProductName as 商品名称, t3.StockId as 仓库ID, t4.StockName  AS 仓库名称             
     
     ---这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

--在select 子句中添加子查询当然快不了
select t0.SkuId,t0. ...... ,Wms1.oldqty,Wms2.newqty,S1.sumadq,S2.sumadq
from 原来的表
inner join wms_stocklog wms1 on wms1.SkuId=t0.SkuId 
inner join wms_stocklog wms2 on wms2.SkuId=t0.SkuId 
inner join
(select a.skuid,b.stockid,sum(c.addedqty) from ........ group by a.skuid,b.stockid)
S1 on s1.skuid=t0.skuid
inner jon
(另一个和)
on...
where not exists(select 1 from ....此处获得 wms_stocklog 表中skuid=t0.skuid 且 id 最小的一个)
and wms1.stockid=t3.stockid
wnd ....

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

 OpeningStock --库存原数量

  ClosingStock--库存最新数量 如果多加这两个字段查询会方便很多，这是题外话

SkuId 是否有索引？
可以改写成 join

join  
(
select  SkuId, StockId, NewQty from dbo.Wms_StockLog w  
where  id=(select max(id) from Wms_StockLog  where SkuId=w.SkuId and  StockId=w.StockId,)
  ) t

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

SQL code
inner join wms_stocklog wms1 on wms1.SkuId=t0.SkuId 
inner join wms_stocklog wms2 on wms2.SkuId=t0.SkuId 
inner join
(select a.skuid,b.stockid,sum(c.addedqty) from ........ group by a.skuid,b.stockid)
S1 on s1.skuid=t0.skuid
) a
inner join
(
select SkuId, StockId, NewQty from dbo.Wms_StockLog w  
where id=(select max(id) from Wms_StockLog where SkuId=w.SkuId and StockId=w.StockId,)
 ) b

------解决方案--------------------
王璐璐 和我之前同事名字一模一样。

------解决方案--------------------
如果在 select 子句中添加子查询,那每扫到一条记录都得去扫描子查询表
如果把子查询表作为一个连接,那只要扫一次就行了.

------解决方案--------------------
2楼,您名字中的那个嫂,很容易让人联想到形状有点类似的另一个字,嗯,不好.

------解决方案--------------------
探讨

引用:
SQL code

--在select 子句中添加子查询当然快不了
select t0.SkuId,t0. ...... ,Wms1.oldqty,Wms2.newqty,S1.sumadq,S2.sumadq
from 原来的表
inner join wms_stocklog wms1 on wms1.SkuId=t0.SkuId
inner join wm……


------解决方案--------------------
可以把执行计划弄上来不！

------解决方案--------------------
连接查询好多啊。可不可以中间过渡一下，先把一定的数据查询出来放临时表，在对临时表查询出最后的结果？

--Try

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