优化一下这句sql ,SELECT c.Contract AS ItemContract , ISNULL(c.ProductCode, '') AS ProductCode , ISNULL(c.ProductCodeDesc, '') AS ProductCodeDesc , a.OrderBomID , a.OrderNo , a.ItemID , b.ItemCode , a.PerNum , QtyPerAssembly , a.SourceBillNo , a.DopID , a.ManufDopId , b.ItemName , b.ItemSpec , b.Units , b.IsIndivisible , b.IsJITMaterial , ISNULL('', 'A01-03-08') AS cBarCode , ISNULL('', 'A') AS CWhCode , ISNULL('', '三楼电子仓') AS CWhName , a.LineItemNo , a.Contract , a.VartualPart , a.Qty , a.ProductLineNo , a.ProductLineDesc , ISNULL(0, 0) AS IsNotGrn , ISNULL(a.[QtySumassembly], 0) AS QtySumassembly , c.PartType FROM ( SELECT OrderNO ,Status FROM dbo.Prod_Order myq WHERE Status <> 7 ) AS a1 INNER JOIN dbo.Prod_OrderBom AS a ON a1.OrderNO = a.OrderNo INNER JOIN dbo.Basal_Item AS b ON b.ItemID = a.ItemID INNER JOIN dbo.Basal_ItemContract AS c ON c.ItemCode = b.ItemCode AND a.Contract = c.Contract WHERE 1=1 AND EXISTS( SELECT 1 FROM Prod_ShopOrder ps WHERE ps.OrderNo=a.SourceBillNo AND (ps.Status='Released' OR ps.Status='Started' OR ps.Status='Reserved')--230211 排除顶层BOM里面关闭的车间订单BOM ) AND EXISTS( SELECT 1 FROM dbo.Prod_ShopOrder ps WHERE ps.MesOrderNo=a1.OrderNo AND ps.IsMesOrder = 'TRUE' AND (ps.Status='Released' OR ps.Status='Started' OR ps.Status='Reserved')--Released Started Reserved )
时间: 2023-03-15 12:54:23 浏览: 174
答:根据您所提供的SQL语句,我建议您优化SQL语句如下:SELECT c.Contract AS ItemContract, ISNULL(c.ProductCode, '') AS ProductCode, ISNULL(c.ProductCodeDesc, '') AS ProductCodeDesc, a.OrderBomID, a.OrderNo, a.ItemID, b.ItemCode, a.PerNum, QtyPerAssembly, a.SourceBillNo, a.DopID, a.ManufDopId, b.ItemName, b.ItemSpec, b.Units, b.IsIndivisible, b.IsJITMaterial, ISNULL('A01-03-08', '') AS cBarCode, ISNULL('A', '') AS CWhCode, ISNULL('三楼电子仓', '') AS CWhName, a.LineItemNo, a.Contract, a.VartualPart, a.Qty, a.ProductLineNo, a.ProductLineDesc, ISNULL(0, 0) AS IsNotGrn, ISNULL(a.[QtySumassembly], 0) AS QtySumassembly, c.PartType FROM Prod_Order AS a1 INNER JOIN Prod_OrderBom AS a ON a1.OrderNO = a.OrderNo INNER JOIN Basal_Item AS b ON b.ItemID = a.ItemID INNER JOIN Basal_ItemContract AS c ON c.ItemCode = b.ItemCode AND a.Contract = c.Contract WHERE EXISTS( SELECT 1 FROM Prod_ShopOrder ps WHERE ps.OrderNo=a.SourceBillNo AND (ps.Status=Released OR ps.Status=Started OR ps.Status=Reserved) AND EXISTS( SELECT 1 FROM dbo.Prod_ShopOrder ps WHERE ps.MesOrderNo=a1.OrderNo AND ps.IsMesOrder = TRUE AND (ps.Status=Released OR ps.Status=Started OR ps.Status=Reserved) );
阅读全文