分析系统订单商品数量和excel里面的数量，因为要插入到数据库里面，如果是excel拉出来字段，自己拼装的话，会显得比较麻烦，我们这里
用sql拼接的方法 拉出来replace语句：
CONCAT(‘replace into ecshop.zhongliang_goods_analyse (barcode,goods_number,status_id,facility_code,type,goods_name) VALUES (\”,
ifnull(gs.barcode,g.barcode),’\’,\”,ifnull(sum(og.goods_number),0),
‘\’,\”,if(og.status_id=’INV_STTS_AVAILABLE’,’良品’,’不良品’),’\’,\”,
(case
when (wm.warehouse_code=’WH_EC_BJ’) then ‘bjcb2c’
when (wm.warehouse_code=’WH_EC_BJ_B2B’) then ‘bjcb2b’
when (wm.warehouse_code=’EC_XM_WH’) then ‘hzb2c’
when (wm.warehouse_code=’EC_GZ_SL’) then ‘gzb2c’
when (wm.warehouse_code=’EC_GZ_SL_B2B’) then ‘gzb2b’
else wm.warehouse_code end),
‘\’,\”,
‘system’,’\’,\”,og.goods_name,’\’)’) as the_sql
from ecshop.ecs_batch_order_info boi
left join ecshop.ecs_batch_order_mapping om ON boi.batch_order_id = om.batch_order_id
left join ecshop.ecs_order_goods og ON om.order_id = og.order_id
left join ecshop.ecs_order_info oi ON og.order_id = oi.order_id
left join ecshop.ecs_goods g ON og.goods_id = g.goods_id
left join ecshop.ecs_goods_style gs ON og.goods_id = gs.goods_id and og.style_id = gs.style_id
left join ecshop.express_best_facility_warehouse_mapping wm ON oi.facility_id = wm.facility_id
where boi.party_id = 65625 and om.is_cancelled=’N’ and om.is_over_c=’N’
group by oi.facility_id,og.goods_id,og.style_id,og.status_id
待插入的表结构
CREATE TABLE ecshop.zhongliang_goods_analyse (
id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
barcode varchar(255) NOT NULL DEFAULT ”,
goods_number int(10) unsigned NOT NULL,
status_id varchar(30) NOT NULL,
facility_code varchar(32) DEFAULT ”,
goods_name varchar(32) DEFAULT ”,
type varchar(32) DEFAULT ”,
PRIMARY KEY (id),
KEY barcode (barcode),
KEY facility_code (facility_code),
KEY status_id (status_id),
KEY type (type)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
最后分析不等的sql，略纠结
— 分析差异数据
select m2.*,
(select ifnull(sum(a2.goods_number),0)
from ecshop.zhongliang_goods_analyse a2 where m2.barcode=a2.barcode
and m2.facility_code=a2.facility_code and m2.status_id = a2.status_id
and a2.type=’zl’) as zl_goods_number,
(select ifnull(sum(a3.goods_number),0)
from ecshop.zhongliang_goods_analyse a3 where m2.barcode=a3.barcode
and m2.facility_code=a3.facility_code and m2.status_id = a3.status_id
and a3.type=’system’) as sys_goods_number,
(select a2.goods_name
from ecshop.zhongliang_goods_analyse a2 where m2.barcode=a2.barcode
and m2.facility_code=a2.facility_code and m2.status_id = a2.status_id
and a2.type=’zl’ limit 1) as zl_goods_name,
(select a3.goods_name
from ecshop.zhongliang_goods_analyse a3 where m2.barcode=a3.barcode
and m2.facility_code=a3.facility_code and m2.status_id = a3.status_id
and a3.type=’system’ limit 1) as sys_goods_name
from
(
select ifnull(sum(if(m.type=’system’,-m.goods_number_total,m.goods_number_total)),0) as number_total,
m.barcode,m.status_id,m.facility_code
from
(
select a.barcode,ifnull(sum(a.goods_number),0) as goods_number_total,a.status_id,a.type,a.facility_code
from ecshop.zhongliang_goods_analyse a
group by a.type,a.facility_code,a.barcode,a.status_id
order by a.barcode,a.status_id,a.facility_code
) m
group by m.barcode,m.status_id,m.facility_code
having number_total <>0
) m2
order by abs(m2.number_total);
