一個mysql查詢問題

一個mysql查詢問題

昨天寫了個對於我來說很復雜的sql語句。 這裡先放個圖：

現在的需要的數據如圖：從product表中拿到id,name兩個字段，從buy_contract_Item和sale_contract_Item中拿到quantity,totalMoney這兩個字段，再從buy_contract和sale_contract中拿到contract_date字段，同時以合同簽定時間為查詢條件將商品各類的數量和金額sum,最後返回獲取結果。表與表的關聯關系如圖。   後來問了下同事，拿到了返回結果： 一開始就犯了個錯誤，雖然從 buy_contract_Item和sale_contract_Item獲取的信息比較多，但卻不能以他們為主表，這是昨天半天還沒有結果的主要原因。因為所有後續的這些表都是以商品為主，為商品的買進和賣出服務的。所以這裡以商品為主表做查詢。    第一步：從product表中拿到Id,Name兩個字段（其他的字段在後面擴充） SELECT id,name FROM product    第二步：從buy_contract_item中拿到productId,SUM(quantity),SUM(totalMoney)這兩個字 段並按組分類 --productId 為了後面與product表做關聯條件的

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

 

第三步：從sale_contract_item中拿到SUM(quantity),SUM(totalMoney)這兩個字段並按組 分類

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

 

第四步： 對第二步的SELECT語句進行擴充，聯結buy_contract： 獲取SUM(quantity),SUM(totalMoney)和contract_date

SELECT productId,SUM(quantity),SUM(totalMoney),contract_date 
FROM  buy_contract_item  a 
JOIN buy_contract b ON a.contract_id = b.id
GROUP BY a.product_id;

 

第五步：添加where查詢條件，另外再加上別名

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

 

第六步：寫出sale的情況和上面類似

SELECT productId,SUM(quantity) AS saleQuantity,SUM(totalMoney) AS saleTotalMoney,d.contract_date 
FROM  buy_contract_item  c 
JOIN buy_contract d ON c.contract_id = d.id
WHERE 1=1  and (d.contract_date >= '2015-03-13'  and d.contract_date <= '2015-03-15')
GROUP BY c.product_id;

 

第一步、第五步、第六步左聯結

SELECT  t.id,t.name,
        IFNULL(buyQuantity,0) AS buyQuantity,
        IFNULL(buyTotalMoney,0) AS buyTotalMoney,
        IFNULL(saleQuantity,0) AS saleQuantity,
        IFNULL(saleTotalMoney,0) AS saleTotalMoney 
FROM product t
LEFT JOIN 
    (
        SELECT productId,SUM(quantity) AS buyQuantity,SUM(totalMoney) AS buyTotalMoney,b.contract_date 
        FROM  buy_contract_item  a 
        JOIN buy_contract b ON a.contract_id = b.id
        WHERE 1=1  and (b.contract_date >= '2015-03-13'  and b.contract_date <= '2015-03-15')
        GROUP BY a.product_id;
    ) m ON t.id = m.productId
LEFT JOIN
    (
        SELECT productId,SUM(quantity) AS saleQuantity,SUM(totalMoney) AS saleTotalMoney,d.contract_date 
        FROM  buy_contract_item  c 
        JOIN buy_contract d ON c.contract_id = d.id
        WHERE 1=1  and (d.contract_date >= '2015-03-13'  and d.contract_date <= '2015-03-15')
        GROUP BY c.product_id;
    ) n ON t.id = n.productId
ORDER BY buyQuantity DESC, saleQuantity DESC

 

    寫的中間可能還有錯誤，但大致思路是這樣，復雜的sql語句，可以先寫整體框架，然後在框架裡不斷的細化查詢。