A mysql query Problem

A mysql query Problem
Yesterday I wrote a complicated SQL statement. The following figure is shown:

The required data now obtains the id and name fields from the product table, the quantity and totalMoney fields from buy_contract_Item and sale_contract_Item, And the contract_date fields from buy_contract and sale_contract, at the same time, sum the quantity and amount of each item based on the Contract Signing time, and return the result. Association between a table and a table. Later I asked my colleagues and got the returned results: I made a mistake at the beginning. Although I got a lot of information from buy_contract_Item and sale_contract_Item, I cannot use them as the primary table, this is the main reason why there was no result for half a day yesterday. Because all subsequent tables are mainly commodities, which serve the purchase and sale of commodities. So here we use the commodity as the main table for query. Step 1: Get the Id and Name fields FROM the product table (other fields are expanded later) SELECT id, name FROM product Step 2: Get productId, SUM (quantity) FROM buy_contract_item ), SUM (totalMoney) fields are classified by group -- productId is used to associate conditions with the product table

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

 

Step 3: Get the SUM (quantity) and SUM (totalMoney) fields from sale_contract_item and classify them by group

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

 

Step 4: Expand the SELECT statement in step 2 and join buy_contract: Get SUM (quantity), SUM (totalMoney) and contract_date

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

 

Step 5: Add a where query condition and an alias

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.idWHERE 1=1  and (b.contract_date >= '2015-03-13'  and b.contract_date <= '2015-03-15')GROUP BY a.product_id;

 

Step 6: Write sale in a similar way as above

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.idWHERE 1=1  and (d.contract_date >= '2015-03-13'  and d.contract_date <= '2015-03-15')GROUP BY c.product_id;

 

Step 1, step 5, step 6 left join

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 tLEFT 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.productIdLEFT 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.productIdORDER BY buyQuantity DESC, saleQuantity DESC

 

There may be errors in the middle of writing, but the general idea is this. For complex SQL statements, you can first write the overall framework and then continuously refine the query in the framework.