etrade_shop_1.0.1_data.sql
1.64 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
-- 此脚本用于开发和测试环境，线上由于是分开的数据库服务器 需要先在旧服务器上造好数据 再同步到新服务器
-- 生产环境使用etrade_shop_1.0.1_data_product.sql
UPDATE `dili_etrade`.`shop` s,
(
SELECT
c.id as 'c_id',
ca.area_id as 'ca_area_id',
ca.area_name as 'ca_area_name',
ca.market_id as 'ca_market_id',
case d.parent_id when 0 then '1' else '2' end as 'level'
FROM `dili-customer`.customer c
inner join `dili-customer`.customer_area ca on c.id = ca.customer_id
left join `dili-basic-data`.district d on ca.area_id = d.id
where ca.area_id is not null and d.parent_id = 0
GROUP BY c_id , ca_market_id
) ta
SET s.district_id=ta.ca_area_id, s.district_name=ta.ca_area_name
WHERE s.customer_id=ta.c_id AND s.market_id=ta.ca_market_id;
UPDATE `dili_etrade`.`shop` s,
(
SELECT
c.id as 'c_id',
dp.id as 'p_ca_area_id',
dp.name as 'p_ca_area_name',
ca.area_id as 'ca_area_id',
ca.area_name as 'ca_area_name',
ca.market_id as 'ca_market_id',
case d.parent_id when 0 then '1' else '2' end as 'level'
FROM `dili-customer`.customer c
inner join `dili-customer`.customer_area ca on c.id = ca.customer_id
left join `dili-basic-data`.district d on ca.area_id = d.id
left join `dili-basic-data`.district dp on d.parent_id = dp.id
where ca.area_id is not null and d.parent_id <> 0
GROUP BY c_id , ca_market_id
) ta
SET s.district_id=ta.p_ca_area_id, s.district_name=ta.p_ca_area_name, s.sub_district_id=ta.ca_area_id, s.sub_district_name=ta.ca_area_name
WHERE s.customer_id=ta.c_id AND s.market_id=ta.ca_market_id;