求教~ 大神来看看这段sql还有救么。用了大量的子查询和关联查询。

[song3213210]

前人的项目里的一段小sql ，查一遍要10分钟之久。。。比这个还复杂的sql 项目里还有好几个。。无奈了。
SELECT
a.sVendorCode,
a.sVendorNameCn,
a.配送中心进货 + a.货到补单,
a.退货,
a.已审核金额,
a.配送中心进货 + a.货到补单 + a.退货 - a.已审核金额,
a.已付款金额,
a.配送中心进货 + a.货到补单 + a.退货 - a.已付款金额,
a.配送中心当前库存金额,
a.直营店当前库存金额,
a.加盟店当前库存金额,
a.美佳当前库存金额,
IF(IFNULL(a.最后供货单审核日期,
'1900-01-01 00:00:00') > IFNULL(a.最后货到补单审核日期,
'1900-01-01 00:00:00'),
a.最后供货单审核日期,
a.最后货到补单审核日期),
IF(IFNULL(a.最后供货单对账日期,
'1900-01-01 00:00:00') > IFNULL(a.最后货到补单对账日期,
'1900-01-01 00:00:00'),
a.最后供货单对账日期,
a.最后货到补单对账日期),
开票金额
FROM
(SELECT
v.sVendorCode,
v.sVendorNameCn,
IFNULL((SELECT
SUM(h.fReceivePurchaseAmount)
FROM
logis_supply_header h
WHERE
h.bEnable = TRUE AND h.fSupplyStatus = 0
AND h.fSupplierId = v.fVendorId), 0) 配送中心进货,
IFNULL((SELECT
SUM(h.fFinalPurchaseAmount)
FROM
logis_delivery_header h
JOIN supp_supplier_wh w ON h.fShipFrom = w.fSupplierWhId
WHERE
h.bEnable = TRUE
AND h.fDeliveryStatus = 0
AND h.sDeliveryType LIKE '%1'
AND w.fSupplierId = v.fVendorId), 0) 货到补单,
IFNULL((SELECT
- SUM(h.fPurchaseAmount)
FROM
logis_return_header h
WHERE
h.bEnable = TRUE AND h.fReturnStatus = 0
AND h.fSupplierId = v.fVendorId), 0) 退货,
IFNULL((SELECT
SUM(b.fTotalPrice)
FROM
vendor_bill b
WHERE
b.bEnable = TRUE
AND b.fVendorId = v.fVendorId), 0) 已审核金额,
IFNULL((SELECT
SUM(b.fPayPrice)
FROM
vendor_bill b
WHERE
b.bEnable = TRUE
AND b.fVendorId = v.fVendorId), 0) 已付款金额,
IFNULL((SELECT
SUM(b.fBillAmout)
FROM
vendor_bill b
WHERE
b.bEnable = TRUE
AND b.fVendorId = v.fVendorId), 0) 开票金额,
IFNULL((SELECT
SUM((o.fQuantity + IFNULL(t.qty, 0)) * spp.fPurchasePrice)
FROM
logis_onhand o
JOIN shop_item i ON i.fItemId = o.fItemId
JOIN shop_product p ON p.fProductId = i.fProductId
JOIN shop_product_price spp ON spp.fProductId = i.fProductId
AND spp.dStartDate <= CURDATE()
AND IFNULL(spp.dEndDate, CURDATE()) >= CURDATE()
LEFT JOIN (SELECT
t.fItemId,
t.fOwnerId,
t.fSubinventoryId,
SUM(t.fTransQuantity) qty
FROM
logis_transaction_temp t
WHERE
t.bEnable = TRUE
AND t.fSubinventoryId > 4
GROUP BY t.fItemId , t.fOwnerId , t.fSubinventoryId) t ON t.fItemId = o.fItemId
AND t.fOwnerId = o.fOwnerId
AND t.fSubinventoryId = o.fSubinventoryId
WHERE
p.fSupplierId = v.fVendorId
AND spp.fVendorId = v.fVendorId
AND o.fSubinventoryId > 4), 0) 配送中心当前库存金额,
IFNULL((SELECT
SUM((o.fQuantity + IFNULL(t.qty, 0)) * spp.fPurchasePrice)
FROM
logis_onhand o
JOIN shop_item i ON i.fItemId = o.fItemId
JOIN shop_product p ON p.fProductId = i.fProductId
JOIN shop_product_price spp ON spp.fProductId = i.fProductId
JOIN comm_store s ON s.fStoreId = o.fOwnerId
AND s.fStoreType = 1
AND spp.dStartDate <= CURDATE()
AND IFNULL(spp.dEndDate, CURDATE()) >= CURDATE()
LEFT JOIN (SELECT
t.fItemId,
t.fOwnerId,
t.fSubinventoryId,
SUM(t.fTransQuantity) qty
FROM
logis_transaction_temp t
WHERE
t.bEnable = TRUE
AND t.fSubinventoryId < 5
GROUP BY t.fItemId , t.fOwnerId , t.fSubinventoryId) t ON t.fItemId = o.fItemId
AND t.fOwnerId = o.fOwnerId
AND t.fSubinventoryId = o.fSubinventoryId
WHERE
p.fSupplierId = v.fVendorId
AND spp.fVendorId = v.fVendorId
AND o.fSubinventoryId < 5), 0) 直营店当前库存金额,
IFNULL((SELECT
SUM((o.fQuantity + IFNULL(t.qty, 0)) * spp.fPurchasePrice)
FROM
logis_onhand o
JOIN shop_item i ON i.fItemId = o.fItemId
JOIN shop_product p ON p.fProductId = i.fProductId
JOIN shop_product_price spp ON spp.fProductId = i.fProductId
JOIN comm_store s ON s.fStoreId = o.fOwnerId
AND s.fStoreType = 2
AND spp.dStartDate <= CURDATE()
AND IFNULL(spp.dEndDate, CURDATE()) >= CURDATE()
LEFT JOIN (SELECT
t.fItemId,
t.fOwnerId,
t.fSubinventoryId,
SUM(t.fTransQuantity) qty
FROM
logis_transaction_temp t
WHERE
t.bEnable = TRUE
AND t.fSubinventoryId < 5
GROUP BY t.fItemId , t.fOwnerId , t.fSubinventoryId) t ON t.fItemId = o.fItemId
AND t.fOwnerId = o.fOwnerId
AND t.fSubinventoryId = o.fSubinventoryId
WHERE
p.fSupplierId = v.fVendorId
AND spp.fVendorId = v.fVendorId
AND o.fSubinventoryId < 5), 0) 加盟店当前库存金额,
IFNULL((SELECT
SUM((o.fQuantity + IFNULL(t.qty, 0)) * spp.fPurchasePrice)
FROM
logis_onhand o
JOIN shop_item i ON i.fItemId = o.fItemId
JOIN shop_product p ON p.fProductId = i.fProductId
JOIN shop_product_price spp ON spp.fProductId = i.fProductId
JOIN comm_store s ON s.fStoreId = o.fOwnerId
AND s.fStoreType = 4
AND spp.dStartDate <= CURDATE()
AND IFNULL(spp.dEndDate, CURDATE()) >= CURDATE()
LEFT JOIN (SELECT
t.fItemId,
t.fOwnerId,
t.fSubinventoryId,
SUM(t.fTransQuantity) qty
FROM
logis_transaction_temp t
WHERE
t.bEnable = TRUE
AND t.fSubinventoryId < 5
GROUP BY t.fItemId , t.fOwnerId , t.fSubinventoryId) t ON t.fItemId = o.fItemId
AND t.fOwnerId = o.fOwnerId
AND t.fSubinventoryId = o.fSubinventoryId
WHERE
p.fSupplierId = v.fVendorId
AND spp.fVendorId = v.fVendorId
AND o.fSubinventoryId < 5), 0) 美佳当前库存金额,
(SELECT
MAX(h.tReceiveDate)
FROM
vendor_billdetail d
JOIN vendor_bill b ON d.fVendorBillId = b.fVendorBillId
JOIN logis_supply_header h ON h.fSupplyHeaderId = d.fOriginId
AND d.sType = '供货单'
WHERE
b.fVendorId = v.fVendorId
AND b.bEnable = TRUE
AND d.bEnable = TRUE) 最后供货单审核日期,
(SELECT
MAX(h.tFinalDate)
FROM
vendor_billdetail d
JOIN vendor_bill b ON d.fVendorBillId = b.fVendorBillId
JOIN logis_delivery_header h ON h.fDeliveryHeaderId = d.fOriginId
AND d.sType = '配送单'
WHERE
b.fVendorId = v.fVendorId
AND b.bEnable = TRUE
AND d.bEnable = TRUE) 最后货到补单审核日期,
(SELECT
MAX(h.tReceiveDate)
FROM
vendor_billdetail d
JOIN vendor_bill b ON d.fVendorBillId = b.fVendorBillId
JOIN logis_supply_header h ON h.fSupplyHeaderId = d.fOriginId
AND d.sType = '供货单'
WHERE
b.fVendorId = v.fVendorId
AND b.bEnable = TRUE
AND d.bEnable = TRUE
AND b.fPayPrice > 0) 最后供货单对账日期,
(SELECT
MAX(h.tFinalDate)
FROM
vendor_billdetail d
JOIN vendor_bill b ON d.fVendorBillId = b.fVendorBillId
JOIN logis_delivery_header h ON h.fDeliveryHeaderId = d.fOriginId
AND d.sType = '配送单'
WHERE
b.fVendorId = v.fVendorId
AND b.bEnable = TRUE
AND d.bEnable = TRUE
AND b.fPayPrice > 0) 最后货到补单对账日期
FROM
comm_vendor v
WHERE
v.sVendorCode LIKE 'CNSP%') a

[zjcxc]

没救了，这个只能自己拆了逐个查查慢在那些地方，然后南做针对性的处理