MySQL 複雜多表關聯查詢實戰：商品採購銷售統計

MySQL 複雜多表關聯查詢實戰：商品採購銷售統計

業務場景

從 product 獲取商品基礎信息，關聯 buy_contract_item、sale_contract_item 統計採購/銷售數量金額，再關聯 buy_contract、sale_contract 篩選合同簽訂時間，最終匯總輸出。

表關係圖解

product (主表) → buy_contract_item/sale_contract_item (明細) → buy_contract/sale_contract (主合同)

分步構建 SQL 思路

1. 基礎商品信息

SELECT id, name FROM product

2. 採購明細聚合

SELECT productId, SUM(quantity), SUM(totalMoney)
FROM buy_contract_item
GROUP BY productId

3. 銷售明細聚合

SELECT productId, SUM(quantity), SUM(totalMoney)
FROM sale_contract_item
GROUP BY productId

4. 採購關聯合同表 + 時間篩選

SELECT a.productId, 
       SUM(a.quantity) AS buyQuantity,
       SUM(a.totalMoney) AS buyTotalMoney,
       b.contract_date
FROM buy_contract_item a
JOIN buy_contract b ON a.contract_id = b.id
WHERE b.contract_date BETWEEN '2015-03-13' AND '2015-03-15'
GROUP BY a.productId

5. 銷售關聯合同表 + 時間篩選

SELECT c.productId,
       SUM(c.quantity) AS saleQuantity,
       SUM(c.totalMoney) AS saleTotalMoney,
       d.contract_date
FROM sale_contract_item c
JOIN sale_contract d ON c.contract_id = d.id
WHERE d.contract_date BETWEEN '2015-03-13' AND '2015-03-15'
GROUP BY c.productId

6. 最終 LEFT JOIN 匯總

SELECT t.id, t.name,
       IFNULL(m.buyQuantity, 0) AS buyQuantity,
       IFNULL(m.buyTotalMoney, 0) AS buyTotalMoney,
       IFNULL(n.saleQuantity, 0) AS saleQuantity,
       IFNULL(n.saleTotalMoney, 0) AS saleTotalMoney
FROM product t
LEFT JOIN ( ...採購子查詢... ) m ON t.id = m.productId
LEFT JOIN ( ...銷售子查詢... ) n ON t.id = n.productId
ORDER BY buyQuantity DESC, saleQuantity DESC

核心經驗總結

• 主表選擇關鍵：以商品為主表，明細表為從表，避免笛卡爾積導致性能災難。
• 分層構建：先寫子查詢聚合，再外層 JOIN，邏輯清晰易維護。
• IFNULL 兜底：無採購/銷售記錄的商品顯示 0 而非 NULL。
• 時間條件下推：在子查詢中過濾合同日期，減少 JOIN 數據量。