档案数据处理.sql
2.38 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- step 1
ALTER TABLE `fw_valhalla`.`customer_base_info`
ADD COLUMN `old_id` bigint(20) NULL AFTER `yn`;
-- step 2
insert into fw_valhalla.customer_base_info(name, member_id, contact, mobile, gender, birthday, id_code, hobby, cus_type,
company_name, address, lat, lng, city_code, city_name, group_id, create_time,
yn,
update_time, old_id)
select ifnull(`name`, '待定') as name,
member_id,
ifnull(`name`, '待定') as contact,
mobile,
ifnull(gender, 3) as gender,
birthday,
id_num as id_code,
hobby,
cus_type,
company_name,
address,
lat,
lng,
city_code,
city_name,
group_id,
create_time,
yn,
now(),
id as old_id
from fw_cas.customer;
-- step 3
insert into fw_valhalla.customer(id, tags, plate_no, frame_no, engine_no, reg_date, spec_code, brand_id,
brand_name, series_id, series_name, spec_id, spec_name, buy_date, buy_price, use_type,
source, group_id, car_image, vehicle_license, current_mileage, shop_id, adviser_id,
create_time, update_time, yn)
select id,
tags,
plate_no,
frame_no,
engine_no,
reg_date,
spec_code,
brand_id,
brand_name,
series_id,
series_name,
spec_id,
spec_name,
buy_date,
buy_price,
use_type,
source,
group_id,
car_image,
vehicle_license,
current_mileage,
shop_id,
adviser_id,
create_time,
now(),
yn
from fw_cas.customer;
-- step 4
update fw_valhalla.customer t1,fw_valhalla.customer_base_info t2
set t1.base_id = t2.id
where t2.old_id = t1.id;
-- step 5
ALTER TABLE `fw_valhalla`.`customer_base_info`
DROP COLUMN `old_id`;
-- step6
insert into `fw_valhalla`.customer_contact(cus_id, `name`, phone, member_id, type, create_time, update_time, relation)
select cus_id,
`name`,
phone,
member_id,
type,
create_time,
update_time,
ifnull(relation, 8) as relation
from fw_cas.customer_contact;
-- step7
update `fw_valhalla`.`customer_base_info` t1
set t1.gender = 3
where t1.gender = 0;