• 欢迎访问小杰博客网站
  • 欢迎访问小杰博客网站哦

mysql用concat生成replace语句,分析库存数据

mysql 小杰 4年前 (2015-01-08) 106次浏览 未收录 1个评论

分析系统订单商品数量和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);

 


小杰博客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:mysql用concat生成replace语句,分析库存数据
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址