急改一上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.****.net/u/20120703/11/e278f81f-58ce-4059-bba0-20b50b2e21fa.html

------解决方案--------------------
又开新帖？真有米，给了份不能解决问题，有点浪费。

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

SQL code


--执行这个语句对比下面的，首先看结果一致不，然后看速度有没有变快
--记得清空缓存
/*
dbcc dropcleanbuffers   
dbcc freeproccache   
dbcc freesystemcache ('ALL','default');

--[语句执行花费时间(毫秒)]
declare @d datetime
set @d=getdate()
/*你的sql脚本开始*/

/*你的sql脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 


*/
--1
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName   
PeriodIn,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
INNER 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_StockLog
GROUP BY skuId,StockId
) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockId


-------------------------
--2
select 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

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

相关推荐