这样的子查询浪费性能吗,如何改进?????????
下面这条语句是查询当前库存的,用初始化库存加入库减出库,求高手改进SELECT dbo.JCXX_Product.id, dbo.JCXX_Warehouse.warName, dbo.JCXX_ProductSpec.prosName, dbo.JCXX_ProductType.protName,
dbo.JCXX_ProductUnit.prouName,
dbo.JCXX_Product.intAmount +
(SELECT ISNULL(SUM(amount),0) from CKMK_DeliveryListDetail INNER JOIN
CKMK_DeliveryList ON deliveryListID=CKMK_DeliveryList.id
WHERE CKMK_DeliveryList.isAudit=1 AND CKMK_DeliveryList.isRevoke<>0 AND productID=JCXX_Product.id
)
+
(SELECT ISNULL(SUM(amount),0) from CKMK_DeliveryListInHouseDetail INNER JOIN
CKMK_DeliveryListInHouse ON deliveryListInHouseID=CKMK_DeliveryListInHouse.id
WHERE CKMK_DeliveryListInHouse.isAudit=1 AND CKMK_DeliveryListInHouse.isRevoke<>0 AND productID=JCXX_Product.id
)
-
(SELECT ISNULL(SUM(amount),0) from CKMK_WarehouseListDetail INNER JOIN
CKMK_WarehouseList ON warehouseListID=CKMK_WarehouseList.id
WHERE CKMK_WarehouseList.isAudit=1 AND CKMK_WarehouseList.isRevoke<>0 AND productID=JCXX_Product.id
)
-
(SELECT ISNULL(SUM(amount),0) from CKMK_WarehouseListInHouseDetail INNER JOIN
CKMK_WarehouseListInHouse ON warehouseListInHouseID=CKMK_WarehouseListInHouse.id
WHERE CKMK_WarehouseListInHouse.isAudit=1 AND CKMK_WarehouseListInHouse.isRevoke<>0 AND productID=JCXX_Product.id
)
AS SumQty , 0 AS Qty
FROM dbo.JCXX_Product INNER JOIN
dbo.JCXX_ProductSpec ON dbo.JCXX_Product.productSpecID = dbo.JCXX_ProductSpec.id INNER JOIN
dbo.JCXX_ProductType ON dbo.JCXX_Product.productTypeID = dbo.JCXX_ProductType.id INNER JOIN
dbo.JCXX_ProductUnit ON dbo.JCXX_Product.productUnitID = dbo.JCXX_ProductUnit.id INNER JOIN
dbo.JCXX_Warehouse ON dbo.JCXX_Product.warehouseID = dbo.JCXX_Warehouse.id
下面这条语句是查询当前库存的,用初始化库存加入库减出库,求高手改进SELECT dbo.JCXX_Product.id, dbo.JCXX_Warehouse.warName, dbo.JCXX_ProductSpec.prosName, dbo.JCXX_ProductType.protName,
dbo.JCXX_ProductUnit.prouName,
dbo.JCXX_Product.intAmount +
(SELECT ISNULL(SUM(amount),0) from CKMK_DeliveryListDetail INNER JOIN
CKMK_DeliveryList ON deliveryListID=CKMK_DeliveryList.id
WHERE CKMK_DeliveryList.isAudit=1 AND CKMK_DeliveryList.isRevoke<>0 AND productID=JCXX_Product.id
)
+
(SELECT ISNULL(SUM(amount),0) from CKMK_DeliveryListInHouseDetail INNER JOIN
CKMK_DeliveryListInHouse ON deliveryListInHouseID=CKMK_DeliveryListInHouse.id
WHERE CKMK_DeliveryListInHouse.isAudit=1 AND CKMK_DeliveryListInHouse.isRevoke<>0 AND productID=JCXX_Product.id
)
-
(SELECT ISNULL(SUM(amount),0) from CKMK_WarehouseListDetail INNER JOIN
CKMK_WarehouseList ON warehouseListID=CKMK_WarehouseList.id
WHERE CKMK_WarehouseList.isAudit=1 AND CKMK_WarehouseList.isRevoke<>0 AND productID=JCXX_Product.id
)
-
(SELECT ISNULL(SUM(amount),0) from CKMK_WarehouseListInHouseDetail INNER JOIN
CKMK_WarehouseListInHouse ON warehouseListInHouseID=CKMK_WarehouseListInHouse.id
WHERE CKMK_WarehouseListInHouse.isAudit=1 AND CKMK_WarehouseListInHouse.isRevoke<>0 AND productID=JCXX_Product.id
)
AS SumQty , 0 AS Qty
FROM dbo.JCXX_Product INNER JOIN
dbo.JCXX_ProductSpec ON dbo.JCXX_Product.productSpecID = dbo.JCXX_ProductSpec.id INNER JOIN
dbo.JCXX_ProductType ON dbo.JCXX_Product.productTypeID = dbo.JCXX_ProductType.id INNER JOIN
dbo.JCXX_ProductUnit ON dbo.JCXX_Product.productUnitID = dbo.JCXX_ProductUnit.id INNER JOIN
dbo.JCXX_Warehouse ON dbo.JCXX_Product.warehouseID = dbo.JCXX_Warehouse.id
也可以用 数据库引擎优化向导 检查SQL. 之后会有些提示或推荐的.
CKMK_DeliveryListDetail 出库表明细
CKMK_WarehouseList 入库表
CKMK_WarehouseListDetail 入库表明细CKMK_DeliveryListInHouse 厂内出库表
CKMK_DeliveryListInHouseDetail 厂内出库表明细
CKMK_WarehouseListInHouse 厂内入库表
CKMK_WarehouseListInHouseDetail 厂内入库表明细要得到产品当前的数量,要用初始化库存加入库减出库