一个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语句,可以先写整体框架,然后在框架里不断的细化查询。
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。