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

mysql备份数据,两表求交集、差集

mysql 小杰 3年前 (2015-04-24) 93次浏览 已收录 0个评论

— 快速备份表的数据
create table ecshop.ecs_express_fee_temp3

select * from ecshop.ecs_express_fee;

两个表之间的差集


replace into ecshop.ecs_express_fee_temp2 
(region_id,carrier_id,facility_id,
first_weight ,
first_fee ,
continued_fee ,
from_region_id ,
tracking_fee ,
operation_fee ,
weighing_fee ,
transit_fee ,
lowest_transit_fee ,
price_weight ,
quality_weight ,
time_arrived_weight ,
service_weight ,
arrived_weight ,
critical_weight )
select 
e.region_id,e.carrier_id,e.facility_id,
e.first_weight ,
e.first_fee ,
e.continued_fee ,
e.from_region_id ,
e.tracking_fee ,
e.operation_fee ,
e.weighing_fee ,
e.transit_fee ,
e.lowest_transit_fee ,
e.price_weight ,
e.quality_weight ,
e.time_arrived_weight ,
e.service_weight ,
e.arrived_weight ,
e.critical_weight 
from ecshop.ecs_express_fee_temp3 e
left join ecshop.ecs_express_fee_temp2 t ON  e.facility_id = t.facility_id and e.region_id = t.region_id and e.carrier_id = t.carrier_id
where t.carriage_id is null and e.facility_id is not null;

两个表之间的交集

update ecshop.ecs_express_fee e
inner join ecshop.ecs_express_fee_temp t ON e.facility_id = t.facility_id and e.region_id = t.region_id and e.carrier_id = t.carrier_id
set 
e.first_weight = t.first_weight,
e.first_fee = t.first_fee,
e.continued_fee = t.continued_fee,
e.from_region_id = t.from_region_id,
e.tracking_fee = t.tracking_fee,
e.operation_fee = t.operation_fee,
e.weighing_fee = t.weighing_fee,
e.transit_fee = t.transit_fee,
e.lowest_transit_fee = t.lowest_transit_fee,
e.price_weight = t.price_weight,
e.quality_weight = t.quality_weight,
e.time_arrived_weight = t.time_arrived_weight,
e.service_weight = t.service_weight,
e.arrived_weight = t.arrived_weight,
e.critical_weight = t.critical_weight
where e.facility_id is not null;


小杰博客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:mysql备份数据,两表求交集、差集
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

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

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