日期:2014-05-18  浏览次数:20448 次

[过来看,过来瞧] 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