急改一上SQL语句的子句！必须是改完整点！可得到95%的分

急急急！改一下SQL语句的子句！必须是改完整点！可得到95%的分

  -----比较卡的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

[解决办法]
又开新帖？真有米，给了份不能解决问题，有点浪费。

建议你把查询分步走来处理，把没一个复杂的查询数据单独放到临时表，
然后最后对临时表进行简单的查询处理
[解决办法]
各个表格连接的列都设为主键和index了没？
这点数据量就卡，肯定是表格设计不合理。
[解决办法]

SQL code

--执行这个语句对比下面的，首先看结果一致不，然后看速度有没有变快--记得清空缓存/*dbcc dropcleanbuffers   dbcc freeproccache   dbcc freesystemcache ('ALL','default');--[语句执行花费时间(毫秒)]declare @d datetimeset @d=getdate()/*你的sql脚本开始*//*你的sql脚本结束*/select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) */--1select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName   PeriodIn,PeriodOutfrom 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.StockIdINNER JOIN /*如果结果不对，把这里改成left join 试试 */(select skuId,StockId,sum(CASE WHEN AddedQty>0 THEN AddedQty ELSE 0 END ) AS PeriodIn ,sum(CASE WHEN AddedQty<0 THEN AddedQty ELSE 0 END ) AS PeriodOut ,from dbo.Wms_StockLogGROUP BY skuId,StockId) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockId---------------------------2select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName    ---下面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
[解决办法]
如果确实想解决问题的话，把你的测试数据准备好，
提供表结构的详细说明，包括字段说明，每张表的主键、外键、索引情况，表的数据量大小，要达到的效果，等等，这样大家才好帮你。
只是给条sql语句，只能就语法层面分析，其它的一些因素是看不到的。
同时可以去看一下执行计划，那个是调优必看的东西。
[解决办法]
你这是个查询吗？
建议  

,(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--库存新数量

这个库存量的能查出来放入到临时表里面

让你这个语句分布来操作。
[解决办法]
SQL code
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  ;with tas(select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc),* from dbo.Wms_StockLog)insert #Wms_StockLogAselect * from t where px=1;with mas(select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc),* from dbo.Wms_StockLogB)insert #Wms_StockLogselect * from mselect skuId,StockIdsum(AddedQty) into #Wms_StockLogCfrom dbo.Wms_StockLogWHERE AddedQty>0group by skuId,StockIdselect skuId,StockIdsum(AddedQty) into #Wms_StockLogDfrom dbo.Wms_StockLogWHERE AddedQty<0group by skuId,StockIdselect *from #Wms_StockLogA ainner join Wms_StockLogB bon a.SkuId=b.SkuId and a.StockId=b.StockIdinner join Wms_StockLogC con a.SkuId=b.SkuId and a.StockId=c.StockIdinner join Wms_StockLogD don a.SkuId=b.SkuId and a.StockId=d.StockId    --try
[解决办法]
SQL code
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName ,  t6.OldQty,t7.NewQty,PeriodIn,PeriodOutfrom 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.StockIdLEFT JOIN  select skuId,StockId,sum(CASE WHEN AddedQty>0 THEN AddedQty ELSE 0 END ) AS PeriodIn ,sum(CASE WHEN AddedQty<0 THEN AddedQty ELSE 0 END ) AS PeriodOut ,from dbo.Wms_StockLogGROUP BY skuId,StockId) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockIdLEFT JOIN(select SkuId, StockId,OldQty from dbo.Wms_StockLog where id=(SELECT min(id) FROM dbo.Wms_StockLog WHERE SkuId=w.SkuId, StockId=w.StockId)) AS t6 ON t0.skuId=t6.SkuId and t0.StockId=t6.StockIdLEFT JOIN(select SkuId, StockId,NewQty from dbo.Wms_StockLog where id=(SELECT max(id) FROM dbo.Wms_StockLog WHERE SkuId=w.SkuId, StockId=w.StockId)) AS t7 ON t0.skuId=t7.SkuId and t0.StockId=t7.StockId--试试这个结果对不对--如果正确，这已是我能尽到的最大能力了--另外的优化就是索引了--1、确保你的所有表都有主键--2、Prod_Sku 表的skuId StockId 有复合索引--3、Wms_StockLog 表的skuId StockId 有复合索引
[解决办法]
SQL code
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  --2005以上版本;with tas(select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc),* from dbo.Wms_StockLog)insert #Wms_StockLogAselect * from t where px=1;with mas(select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc),* from dbo.Wms_StockLogB)insert #Wms_StockLogselect * from mselect skuId,StockIdsum(AddedQty) as AddedQty into #Wms_StockLogCfrom dbo.Wms_StockLogWHERE AddedQty>0group by skuId,StockIdselect skuId,StockIdsum(AddedQty) as AddedQty into #Wms_StockLogDfrom dbo.Wms_StockLogWHERE AddedQty<0group by skuId,StockIdselect *from #Wms_StockLogA ainner join Wms_StockLogB bon a.SkuId=b.SkuId and a.StockId=b.StockIdinner join Wms_StockLogC con a.SkuId=b.SkuId and a.StockId=c.StockIdinner join Wms_StockLogD don a.SkuId=b.SkuId and a.StockId=d.StockId--2000select * into #Wms_StockLogA from Wms_StockLog a where id=(select MIN(id) from Wms_StockLog bwhere a.SkuId=b.SkuId and a.StockId=b.StockId )select * into #Wms_StockLogb from Wms_StockLog a where id=(select MAX(NewQty) from Wms_StockLog bwhere a.SkuId=b.SkuId and a.StockId=b.StockId)select skuId,StockIdsum(AddedQty) as AddedQty into #Wms_StockLogCfrom dbo.Wms_StockLogWHERE AddedQty>0group by skuId,StockIdselect skuId,StockIdsum(AddedQty) as AddedQty into #Wms_StockLogDfrom dbo.Wms_StockLogWHERE AddedQty<0group by skuId,StockIdselect *from #Wms_StockLogA ainner join Wms_StockLogB bon a.SkuId=b.SkuId and a.StockId=b.StockIdinner join Wms_StockLogC con a.SkuId=b.SkuId and a.StockId=c.StockIdinner join Wms_StockLogD don a.SkuId=b.SkuId and a.StockId=d.StockId--有语法问题自己调试一下，我这没表结构没法调试