cps-doc/10技术支持文档/cps商户费率拆分修改sql.txt

ALTER TABLE merch_rate_pkg_op ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
ALTER TABLE merch_rate_pkg_op ADD COLUMN cal_formula_pkg_op CHAR(1) DEFAULT NULL COMMENT '计算公式:0:单一;1:阶段' AFTER biz_type;
ALTER TABLE merch_rate_pkg_op ADD COLUMN order_num_range_min int DEFAULT NULL COMMENT '订单票数范围最小值' AFTER cal_formula_pkg_op;
ALTER TABLE merch_rate_pkg_op ADD COLUMN order_num_range_max int DEFAULT NULL COMMENT '订单票数范围最大值' AFTER order_num_range_min;
UPDATE merch_rate_pkg_op SET biz_type='00',cal_formula_pkg_op='0';



ALTER TABLE merch_rate_expr_yunda ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
ALTER TABLE merch_rate_expr_yunda ADD COLUMN cal_formula_express CHAR(1) DEFAULT NULL COMMENT '计算公式:0:重量;1:面单' AFTER biz_type;
UPDATE merch_rate_expr_yunda SET biz_type='00',cal_formula_express='0';

ALTER TABLE merch_rate_expr_sf ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
ALTER TABLE merch_rate_expr_sf ADD COLUMN cal_formula_express CHAR(1) DEFAULT NULL COMMENT '计算公式:0:重量;1:面单' AFTER biz_type;
UPDATE merch_rate_expr_sf SET biz_type='00',cal_formula_express='0';

ALTER TABLE merch_rate_expr_yto ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
ALTER TABLE merch_rate_expr_yto ADD COLUMN cal_formula_express CHAR(1) DEFAULT NULL COMMENT '计算公式:0:重量;1:面单' AFTER biz_type;
UPDATE merch_rate_expr_yto SET biz_type='00',cal_formula_express='0';

ALTER TABLE merch_rate_expr_zto ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
ALTER TABLE merch_rate_expr_zto ADD COLUMN cal_formula_express CHAR(1) DEFAULT NULL COMMENT '计算公式:0:重量;1:面单' AFTER biz_type;
UPDATE merch_rate_expr_zto SET biz_type='00',cal_formula_express='0';

ALTER TABLE merch_rate_expr_cpam ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
ALTER TABLE merch_rate_expr_cpam ADD COLUMN cal_formula_express CHAR(1) DEFAULT NULL COMMENT '计算公式:0:重量;1:面单' AFTER biz_type;
UPDATE merch_rate_expr_cpam SET biz_type='00',cal_formula_express='0';


ALTER TABLE merch_rate_expr_add_yunda ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
UPDATE merch_rate_expr_add_yunda SET biz_type='00';

ALTER TABLE merch_rate_expr_add_yto ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER merch_sn;
UPDATE merch_rate_expr_add_yto SET biz_type='00';


ALTER TABLE supplier_rate_expr_yunda ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER comp_sn;
UPDATE supplier_rate_expr_yunda SET biz_type='00';

ALTER TABLE supplier_rate_expr_sf ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER comp_sn;
UPDATE supplier_rate_expr_sf SET biz_type='00';

ALTER TABLE supplier_rate_expr_yto ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER comp_sn;
UPDATE supplier_rate_expr_yto SET biz_type='00';

ALTER TABLE supplier_rate_expr_zto ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER comp_sn;
UPDATE supplier_rate_expr_zto SET biz_type='00';

ALTER TABLE supplier_rate_expr_cpam ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER comp_sn;
UPDATE supplier_rate_expr_cpam SET biz_type='00';

ALTER TABLE supplier_rate_expr_add_yunda ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER supp_yunda_add_rate_sn;
UPDATE supplier_rate_expr_add_yunda SET biz_type='00';

ALTER TABLE supplier_rate_expr_add_yto ADD COLUMN biz_type VARCHAR(2) DEFAULT NULL COMMENT 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部' AFTER supp_yto_add_rate_sn;
UPDATE supplier_rate_expr_add_yto SET biz_type='00';


drop index index_merch_type_sp_cp_cc on merch_rate_expr_yunda;

create index index_merch_biz_expr_sp_cp_cc on merch_rate_expr_yunda
(
   merch_sn,
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_merch_type_sp_cp_cc on merch_rate_expr_sf;

create index index_merch_biz_expr_sp_cp_cc on merch_rate_expr_sf
(
   merch_sn,
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_merch_type_sp_cp_cc on merch_rate_expr_yto;

create index index_merch_biz_expr_sp_cp_cc on merch_rate_expr_yto
(
   merch_sn,
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_merch_type_sp_cp_cc on merch_rate_expr_zto;

create index index_merch_biz_expr_sp_cp_cc on merch_rate_expr_zto
(
   merch_sn,
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_merch_type_sp_cp_cc on merch_rate_expr_cpam;

create index index_merch_biz_expr_sp_cp_cc on merch_rate_expr_cpam
(
   merch_sn,
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);


drop index index_type_sp_cp_cc on supplier_rate_expr_yunda;

create index index_biz_expr_sp_cp_cc on supplier_rate_expr_yunda
(
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_type_sp_cp_cc on supplier_rate_expr_sf;

create index index_biz_expr_sp_cp_cc on supplier_rate_expr_sf
(
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_type_sp_cp_cc on supplier_rate_expr_yto;

create index index_biz_expr_sp_cp_cc on supplier_rate_expr_yto
(
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_type_sp_cp_cc on supplier_rate_expr_zto;

create index index_biz_expr_sp_cp_cc on supplier_rate_expr_zto
(
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

drop index index_type_sp_cp_cc on supplier_rate_expr_cpam;

create index index_biz_expr_sp_cp_cc on supplier_rate_expr_cpam
(
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);

ALTER TABLE tmp_cp_data_order_pkg ADD COLUMN cal_formula_express CHAR(1) DEFAULT NULL COMMENT '快递费计算公式:0:重量;1:面单,计算成功才会有值' AFTER weight;