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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
| update baseinfo_tb_b_carclass m INNER JOIN (
select brand_id,class_id ,ROW_NUMBER() OVER (PARTITION BY BRAND_ID ORDER BY class_id,class_name) AS sub_index
from baseinfo_tb_b_carclass m1
GROUP BY brand_id ,class_id
order by brand_id,sub_index
) mi
on mi.class_id=m.class_id
set m.show_order=mi.sub_index;
update baseinfo_tb_b_carclass set show_order=10;
update baseinfo_tb_b_carclass m INNER JOIN (
select brand_id,class_id ,ROW_NUMBER() OVER (PARTITION BY BRAND_ID ORDER BY class_id,class_name) AS sub_index
from baseinfo_tb_b_carclass m1
GROUP BY brand_id ,class_id
order by brand_id,sub_index
) mi
on mi.class_id=m.class_id
set m.show_order=mi.sub_index;
select m1.id,m1.dim,m1.dim_real,m1.cal_order,m1.show_order, m1.cal_order + mi.sub_index * POW(100, mi.pow),mi.pow,mi.sub_index,POW(100, mi.pow)
from cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code, sub_index,pow
from cem_metrics m2
where m2.dim like "%_$" and m2.sub_code is not null and m2.sub_code !='0' ) mi
on mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
where m1.variable=1
and m1.sub_code is not null
and m1.sub_code !='0'
and m1.store = 'nm00012164'
and m1.month=4
and m1.dim like 'REV_YS_FIN_NC_FY_$%';
select m1.id,m1.dim,m1.dim_real,m1.cal_order,m1.show_order, m1.cal_order + mi.sub_index * POW(100, mi.pow),mi.pow,mi.sub_index,POW(100, mi.pow)
from cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code, sub_index,pow
from cem_metrics m2
where m2.dim like "%_$" and m2.sub_code is not null and m2.sub_code !='0' ) mi
on mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
where m1.variable=1
and m1.sub_code is not null
and m1.sub_code !='0'
and m1.store = 'nm00012164'
and m1.month=6
and m1.dim like 'REV_YS_FIN_NC_FY_%';
update cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code, sub_index,pow
from cem_metrics m2
where m2.dim like "%_$" and m2.sub_code is not null and m2.sub_code !='0' ) mi
on mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
set m1.show_order=m1.cal_order + mi.sub_index * POW(100, 2)
where m1.variable=1
and m1.sub_code is not null
and m1.sub_code !='0'
and m1.store = 'nm00012164'
and m1.month=4
and m1.dim like 'BAS_NC_INVCNT_DEC_SUB%';
update cem_metrics m
INNER JOIN cem_dim d
on d.code = m.dim
set m.name = d.name,m.cal_order=d.cal_order,m.show_order=d.cal_order,m.dim_parent = d.parent_id
,m.dim_root=d.root_id,m.pow=d.pow,m.variable=d.variable,m.val_type=d.val_type ;
update cem_metrics m INNER JOIN (select store , dim, sub_code,ROW_NUMBER() OVER (PARTITION BY store,dim ORDER BY store,dim) AS sub_index
from cem_metrics m1
where dim like "%_$" and sub_code is not null and sub_code !='0'
GROUP BY store,dim, sub_code
order by store,dim, sub_code) mi
on mi.store=m.store and mi.dim=m.dim and mi.sub_code = m.sub_code
set m.sub_index=mi.sub_index;
update cem_metrics m
INNER JOIN cem_dim dim on dim.code = m.dim and dim.has_sub_detail=1 and dim.sub_detail_type=1
inner join baseinfo_tb_b_carclass c on c.CLASS_CODE =m.sub_code
set m.sub_index =c.show_order;
update cem_metrics set show_order=cal_order;
select m1.id,m1.dim,m1.dim_root,m1.cal_order,m1.pow,mi.cal_order,mi.show_order,CONVERT(mi.show_order + m1.sub_index * POW(100, m1.pow),char)
from cem_metrics m1 INNER JOIN (select distinct store , dim,dim_root, sub_code, sub_index,pow,cal_order,show_order
from cem_metrics m2
where m2.dim like "%_$" and m2.dim_root=m2.dim and m2.sub_code is not null and m2.sub_code !='0' ) mi
on mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
where m1.variable=1
and m1.dim != m1.dim_root
and m1.dim ='REV_NC_SALE_RETAIL_$_AVG'
and m1.store='nm00012164'
and m1.`month`=6;
update cem_metrics
set show_order=cal_order + sub_index * POW(100, pow)
where dim like "%_$" and dim_root=dim and sub_code is not null and sub_code !='0'
and variable=1;
update cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code, sub_index,pow,cal_order,show_order
from cem_metrics m2
where m2.dim like "%_$" and m2.dim_root=m2.dim and m2.sub_code is not null and m2.sub_code !='0' ) mi
on mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
set m1.show_order=mi.show_order + m1.sub_index * POW(100, m1.pow)
where m1.variable=1
and m1.dim != m1.dim_root;
update cem_metrics m
set m.show_name = m.name ;
update cem_metrics m
set m.show_name = m.sub_name
where m.dim like "%_$" and m.sub_code !='0' and m.sub_code is not null;
update cem_metrics set dim_real = dim where variable=0;
update cem_metrics set dim_real =replace(dim,"$",sub_code) where variable=1;
update cem_metrics set dim_real_parent = dim_parent where variable=0;
update cem_metrics set dim_real_parent =replace(dim_parent,"$",sub_code) where variable=1;
|