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.

Then I asked my colleague and got the returned result:
At the beginning, I made a mistake. 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 I had no results 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: SELECT productId, SUM (quantity), SUM (totalMoney) FROM buy_contract_item group by productId Step 3: Get the SUM (quantity), SUM (totalMoney) fields FROM sale_contract_item and SELECT productId, SUM (quantity), SUM (totalMoney) BY GROUP) FROM sale_contract_item GROU P by productId Step 4: Expand the SELECT statement in step 2 and join the buy_contract: Get SUM (quantity), SUM (totalMoney) and contract_dateSELECT productId, SUM (quantity ), SUM (totalMoney), contract_date FROM buy_contract_item a JOIN buy_contract B ON. contract_id = B. idGROUP BY. step 5: add the where query condition, and add the alias SELECT productId, SUM (quantity) AS buyQuantity, SUM (totalMoney) AS buyTotalMoney, B. contract_date FROM buy_contract_item a JOIN buy _ Contract B ON. contract_id = B. idWHERE 1 = 1 and (B. contract_date> = '2014-03-13 'and B. contract_date <= '2014-03-15 ') group by. product_id; Step 6: Write sale, which is similar to 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> = '2014-03-13 'and d. contract_date <= '2017-03 -15') group by c. product_id; Step 1, step 5, and 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. contr Act_id = B. id WHERE 1 = 1 and (B. contract_date> = '2014-03-13 'and B. contract_date <= '2014-03-15 ') group by. 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> = '2014-03-13 'and d. contract_date <= '2017- 03-15 ') group by c. product_id;) n ON t. id = n. productIdORDER BY buyQuantity DESC and saleQuantity DESC may have errors in writing, but the general idea is that complex SQL statements can first write the overall framework and then continuously refine the query in the framework.