第一
1.修正销售客户创建者的sql脚本:
UPDATE e_sales_customers sc SET sc.user_id = ( SELECT tmp.user_id FROM ( SELECT sales_customers_id, sales_customers_contact_authorized_person, GROUP_CONCAT(user_id) user_id, GROUP_CONCAT(user_name) user_name FROM e_sales_customers_contact GROUP BY sales_customers_id ) tmp WHERE tmp.sales_customers_id = sc.id ), sc.user_name = ( SELECT tmp.user_name FROM ( SELECT sales_customers_id, sales_customers_contact_authorized_person, GROUP_CONCAT(user_id) user_id, GROUP_CONCAT(user_name) user_name FROM e_sales_customers_contact GROUP BY sales_customers_id ) tmp WHERE tmp.sales_customers_id = sc.id );
注意:
使用了group_concat()函数和group查询;使用了子查询;
2.修正销售订单退货数据:
UPDATE e_sales_order_refund sor SET sor.user_name = (SELECT name from e_user WHERE username=sor.user_name);
第二
库存盘点修正入库单的入库时间sql
SELECT common_producer_info_name,GROUP_CONCAT(wms_material_in_sheet_number) FROM e_wms_material_in_sheet WHERE wms_material_in_sheet_product_in_date is NULL AND is_del = 0 AND wms_material_in_sheet_status = 1 GROUP BY common_producer_info_id; SELECT common_producer_info_name,GROUP_CONCAT(wms_material_out_sheet_number) FROM e_wms_material_out_sheet WHERE wms_material_out_sheet_outgoing_date is NULL AND is_del = 0 AND wms_material_out_sheet_status = 1 GROUP BY common_producer_info_id; SELECT common_producer_info_name,GROUP_CONCAT(wms_product_in_sheet_number) FROM e_wms_product_in_sheet WHERE wms_product_in_sheet_product_in_date is NULL AND is_del = 0 AND wms_product_in_sheet_status = 1 GROUP BY common_producer_info_id; SELECT common_producer_info_name,GROUP_CONCAT(wms_product_out_sheet_number) FROM e_wms_product_out_sheet WHERE wms_product_out_sheet_product_out_date is NULL AND is_del = 0 AND wms_product_out_sheet_status = 1 GROUP BY common_producer_info_id; UPDATE e_wms_material_in_sheet SET wms_material_in_sheet_product_in_date = updated_at WHERE wms_material_in_sheet_product_in_date is NULL AND is_del = 0; UPDATE e_wms_material_out_sheet SET wms_material_out_sheet_outgoing_date = updated_at WHERE wms_material_out_sheet_outgoing_date is NULL AND is_del = 0; UPDATE e_wms_product_in_sheet SET wms_product_in_sheet_product_in_date = updated_at WHERE wms_product_in_sheet_product_in_date is NULL AND is_del = 0; UPDATE e_wms_product_out_sheet SET wms_product_out_sheet_product_out_date = updated_at WHERE wms_product_out_sheet_product_out_date is NULL AND is_del = 0; UPDATE e_wms_product_out_sheet SET wms_product_out_sheet_product_out_date = UNIX_TIMESTAMP('2017-11-21') WHERE wms_product_out_sheet_number = `CPCK-002-171222-0039`