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

[雷肿么了]

//以下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 

[黄_瓜]

这嵌套子查询也太多了点吧

[雷肿么了]

是有点多，不过就4张表
tStock_ShopNum,
tStock_Supplier,
tStock_Goods,
tStock_GoodsSku

[雷肿么了]

tStock_ShopNum,
tStock_PageLog,
tStock_Goods,
tStock_GoodsSku,
tStock_GoodsSkuNum

发错了，其实用到的就5张表。

[超凡]

改用表联接吧！

[jujia007]

不知道怎么说，嵌套到头晕啊

[雷肿么了]

哈哈，之前同事写的。
要改改。

[开着拖拉机泡妞]

吧嵌套查询改了，使用表与表之间的关联，你这个嵌套太多了

[Quote=引用楼主 的回复:]
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……
[/Quote]

[dovei]

改用left join吧

[sdudubing]

[Quote=引用 8 楼 的回复:]

改用left join吧
[/Quote]
+

[tanliangwei520]

我觉得先得熟悉表结构，然后在考虑该怎么查。

[tanliangwei520]

楼主要把问题描述清楚，这样别人也好参与讨论，光贴代码，很难看明白...

[血痕123456]

这个是牛人，这么多的子查询嵌套·····

[Mr_Nice]

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 

这样看就清楚多了。中间isnull判断的地方子查询用的太多了。 可以考虑用连接表的方式解决。