[过来看，过来瞧] SQL 语句优化,该怎么解决-MSSQL教程-爱易网页

[过来看，过来瞧] SQL 语句优化,该怎么解决

日期：2014-05-18　浏览次数：20655 次

[过来看，过来瞧] SQL 语句优化

SQL code


//以下SQL 语句，不知各位会怎么优化
select tStock_GoodsSku.CodeNum,tStock_Goods.CodeNum,tStock_GoodsSku.CodeN,tStock_Goods.Name,
isnull((select top 1 nowcost from tStock_PageLog where tStock_PageLog.GoodsSkuId=tStock_GoodsSku.Id and tStock_PageLog.Type=1  order by ID desc ),0),
isnull((select SUM(Num_MachineTo) from tStock_GoodsSkuNum where tStock_GoodsSkuNum.GoodsSkuId=tStock_GoodsSku.Id ),0),
isnull(tStock_GoodsSku.Num,0), 
isnull((select SUM(Num_LeaveGoods) from tStock_GoodsSkuNum where tStock_GoodsSkuNum.GoodsSkuId=tStock_GoodsSku.Id ),0),
isnull((select sum(num) from tStock_GoodsSkuNum where tStock_GoodsSkuNum.GoodsSkuId=tStock_GoodsSku.Id  ),0),
isnull((select top 1 tStock_Stock.StockName from  tStock_GoodsSkuNum,tStock_Stock where tStock_GoodsSkuNum.GoodsSkuId=tStock_GoodsSku.Id and tStock_Stock.Id=tStock_GoodsSkuNum.StockId order by tStock_GoodsSkuNum.Num desc),'无')
 from (select GoodsSkuId from tStock_ShopNum where PlanStatus>0 and PlanStatus<=40 )tStock_ShopNum,tStock_Supplier,tStock_Goods,tStock_GoodsSku 
 where tStock_ShopNum.GoodsSkuId=tStock_GoodsSku.Id and tStock_Goods.Id=tStock_GoodsSku.GoodsId

------解决方案--------------------
这嵌套子查询也太多了点吧
------解决方案--------------------
吧嵌套查询改了，使用表与表之间的关联，你这个嵌套太多了

探讨
SQL code

//以下SQL 语句，不知各位会怎么优化
select tStock_GoodsSku.CodeNum,tStock_Goods.CodeNum,tStock_GoodsSku.CodeN,tStock_Goods.Name,
isnull((select top 1 nowcost from tStock_PageLog where tStock_PageLog.Goods……

------解决方案--------------------
我觉得先得熟悉表结构，然后在考虑该怎么查。
------解决方案--------------------
楼主要把问题描述清楚，这样别人也好参与讨论，光贴代码，很难看明白...
------解决方案--------------------
这个是牛人，这么多的子查询嵌套·····
------解决方案--------------------

SQL code

SELECT  tStock_GoodsSku.CodeNum ,
        tStock_Goods.CodeNum ,
        tStock_GoodsSku.CodeN ,
        tStock_Goods.Name ,
        ISNULL(( SELECT TOP 1
                        nowcost
                 FROM   tStock_PageLog
                 WHERE  tStock_PageLog.GoodsSkuId = tStock_GoodsSku.Id
                        AND tStock_PageLog.Type = 1
                 ORDER BY ID DESC
               ), 0) ,
        ISNULL(( SELECT SUM(Num_MachineTo)
                 FROM   tStock_GoodsSkuNum
                 WHERE  tStock_GoodsSkuNum.GoodsSkuId = tStock_GoodsSku.Id
               ), 0) ,
        ISNULL(tStock_GoodsSku.Num, 0) ,
        ISNULL(( SELECT SUM(Num_LeaveGoods)
                 FROM   tStock_GoodsSkuNum
                 WHERE  tStock_GoodsSkuNum.GoodsSkuId = tStock_GoodsSku.Id
               ), 0) ,
        ISNULL(( SELECT SUM(num)
                 FROM   tStock_GoodsSkuNum
                 WHERE  tStock_GoodsSkuNum.GoodsSkuId = tStock_GoodsSku.Id
               ), 0) ,
        ISNULL(( SELECT TOP 1
                        tStock_Stock.StockName
                 FROM   tStock_GoodsSkuNum ,
                        tStock_Stock
                 WHERE  tStock_GoodsSkuNum.GoodsSkuId = tStock_GoodsSku.Id
                        AND tStock_Stock.Id = tStock_GoodsSkuNum.StockId
                 ORDER BY tStock_GoodsSkuNum.Num DESC
               ), '无')
FROM    ( SELECT    GoodsSkuId
          FROM      tStock_ShopNum
          WHERE     PlanStatus > 0
                    AND PlanStatus <= 40
        ) tStock_ShopNum ,
        tStock_Supplier ,
        tStock_Goods ,
        tStock_GoodsSku
WHERE   tStock_ShopNum.GoodsSkuId = tStock_GoodsSku.Id
        AND tStock_Goods.Id = tStock_GoodsSku.GoodsId

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

[过来看，过来瞧] SQL 语句优化,该怎么解决

相关资料更多>

推荐阅读更多>