前人的專案里的一段小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
uj5u.com熱心網友回復：
沒救了，這個只能自己拆了逐個查查慢在那些地方，然后南做針對性的處理轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103697.html
標籤：MySQL