多条数据聚合sql

今天写了一个商品库存的查询，一个商品对应多个库存（多个仓库）（库存类型 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

 

转载于:https://www.cnblogs.com/xiaoxiaojuan/p/10120083.html