今天写了一个商品库存的查询，一个商品对应多个库存（多个仓库）（库存类型 1001销售库;1002残次品库;1003退货库），其中相同的仓库的库存数据相加，得到一条包含商品主数据、仓库库存清单列表：

sql如下：

SELECT pcc.* from (

SELECT DISTINCT pst.spu_no,pst.sku_no,pst.product_name,pst.category_code,pst.category_name,pst.picture_url,pst.original_price,pst.unit,pst.product_type, pro.*, SUM(IF(pro.stock_type = 1001, pro.pro_sum, 0)) AS stockType1,

        SUM(IF(pro.stock_type = 1002, pro.pro_sum, 0)) AS stockType2,

        SUM(IF(pro.stock_type = 1003, pro.pro_sum, 0)) AS stockType3  

from(SELECT * from inventory_book ps)pro

 INNER JOIN local_product pst on pro.barcode=pst.barcode

GROUP BY pst.barcode) pcc

当时写的有点罗嗦其实 就是俩表联查取出

SELECT  pst.spu_no,pst.sku_no,pst.product_name,

SUM(IF(pro.stock_type = 1001, pro.pro_sum, 0)) AS stockType1,

        SUM(IF(pro.stock_type = 1002, pro.pro_sum, 0)) AS stockType2,

        SUM(IF(pro.stock_type = 1003, pro.pro_sum, 0)) AS stockType3  

from inventory_book pro

 INNER JOIN local_product pst on pro.barcode=pst.barcode

GROUP BY pst.barcode