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

【小杰说mysql】mysql计算面单快递费

mysql 小杰 4年前 (2015-01-31) 151次浏览 已收录 0个评论

mysql计算快递费,首重续重:

— 申通

select
oi.order_time,
s.tracking_number as ‘运单号’,
 oi.taobao_order_sn as ‘淘宝订单号’,
 pa.pay_name as ‘支付方式’,
p.name as ‘组织’,f.facility_name as ‘仓库’,
es.shipping_name as ‘订单快递’,
oi.shipping_id,es.default_carrier_id as carrier_id,
oi.facility_id,
if(oi.district <>’0′,oi.district,if(oi.city <>’0′,oi.city,oi.province)) as region_id,
c.first_fee as ‘首重费’,
c.continued_fee as ‘续重费’,
c.first_weight as ‘首重’,
s.shipping_leqee_weight/1000 as ‘系统重量’,
cw.weight as ‘快递公司重量’,

if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,

‘22143846’,’22143847′,’24196974′,’76065524′),

if( ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight > 0,ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight ,0),
if( ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight) > 0,ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight),0)
)
 as ‘系统续重的重量’,
ifnull( format( (c.first_fee +

if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,

‘22143846’,’22143847′,’24196974′,’76065524′),

if( ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight > 0,ceil(s.SHIPPING_LEQEE_WEIGHT/1000 *2)/2 – c.first_weight ,0),
if( ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight) > 0,ceil((s.SHIPPING_LEQEE_WEIGHT/1000 – c.first_weight)/c.first_weight),0)
)
* c.continued_fee),2 ),0) as ‘系统快递费’,

if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,

‘22143846’,’22143847′,’24196974′,’76065524′),

if( ceil(cw.weight *2)/2 – c.first_weight > 0,ceil(cw.weight *2)/2 – c.first_weight ,0),
if( ceil((cw.weight – c.first_weight)/c.first_weight) > 0,ceil((cw.weight – c.first_weight)/c.first_weight),0)
)
 as ‘快递公司续重的重量’,
ifnull( format( (c.first_fee +

if(oi.facility_id in(‘19568548′,’3580047′,’49858449′,’19568549′,’3633071’,

‘22143846’,’22143847′,’24196974′,’76065524′),

if( ceil(cw.weight *2)/2 – c.first_weight > 0,ceil(cw.weight *2)/2 – c.first_weight ,0),
if( ceil((cw.weight – c.first_weight)/c.first_weight) > 0,ceil((cw.weight – c.first_weight)/c.first_weight),0)
)
* c.continued_fee),2 ),0) as ‘快递公司快递费’
FROM
romeo.company_tracking_weight cw
inner join romeo.shipment s ON cw.tracking_number = s.tracking_number
inner JOIN romeo.order_shipment os ON s.shipment_id = os.shipment_id
inner JOIN ecshop.ecs_order_info oi ON os.order_id = oi.order_id
inner JOIN romeo.party p ON p.party_id = convert(oi.party_id using utf8)
inner JOIN ecshop.ecs_payment pa ON pa.pay_id = oi.pay_id
inner JOIN ecshop.ecs_shipping es ON es.shipping_id = oi.shipping_id
inner JOIN ecshop.ecs_shipping es2 ON es2.shipping_id = s.shipment_type_id
inner JOIN romeo.facility f ON  f.facility_id = oi.facility_id
left JOIN ecshop.ecs_carriage c ON c.carrier_id = oi.shipping_id
 and oi.facility_id = c.facility_id
and if(oi.district <>’0′,oi.district,if(oi.city <>’0′,oi.city,oi.province)) = c.region_id
where oi.shipping_id = 89

group by cw.tracking_number

这样可以根据面单号及系统维护的首重续重规则,快递公司给的重量来计算快递费了!


小杰博客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:【小杰说mysql】mysql计算面单快递费
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

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

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