1 | CREATE VIEW vw_cross_class as |
---|
2 | SELECT T1.class_id AS class_id1, |
---|
3 | T2.class_id AS class_id2, |
---|
4 | T1.classcategory_id AS classcategory_id1, |
---|
5 | T2.classcategory_id AS classcategory_id2, |
---|
6 | T1.name AS name1, |
---|
7 | T2.name AS name2, |
---|
8 | T1.rank AS rank1, |
---|
9 | T2.rank AS rank2 |
---|
10 | FROM dtb_classcategory AS T1, |
---|
11 | dtb_classcategory AS T2; |
---|
12 | |
---|
13 | CREATE VIEW vw_cross_products_class AS |
---|
14 | SELECT T1.class_id1, |
---|
15 | T1.class_id2, |
---|
16 | T1.classcategory_id1, |
---|
17 | T1.classcategory_id2, |
---|
18 | T2.product_id, |
---|
19 | T1.name1, |
---|
20 | T1.name2, |
---|
21 | T2.product_code, |
---|
22 | T2.stock, |
---|
23 | T2.price01, |
---|
24 | T2.price02, |
---|
25 | T1.rank1, |
---|
26 | T1.rank2 |
---|
27 | FROM vw_cross_class AS T1 |
---|
28 | LEFT JOIN dtb_products_class AS T2 |
---|
29 | ON T1.classcategory_id1 = T2.classcategory_id1 |
---|
30 | AND T1.classcategory_id2 = T2.classcategory_id2; |
---|
31 | |
---|
32 | CREATE VIEW vw_products_nonclass AS |
---|
33 | SELECT * |
---|
34 | FROM dtb_products AS T1 LEFT JOIN |
---|
35 | (SELECT |
---|
36 | product_id AS product_id_sub, |
---|
37 | product_code, |
---|
38 | price01, |
---|
39 | price02, |
---|
40 | stock, |
---|
41 | stock_unlimited, |
---|
42 | classcategory_id1, |
---|
43 | classcategory_id2 |
---|
44 | FROM dtb_products_class |
---|
45 | WHERE classcategory_id1 = 0 |
---|
46 | AND classcategory_id2 = 0) AS T2 |
---|
47 | ON T1.product_id = T2.product_id_sub; |
---|
48 | |
---|
49 | CREATE VIEW vw_products_allclass AS |
---|
50 | SELECT T5.product_id, |
---|
51 | product_code_min, |
---|
52 | product_code_max, |
---|
53 | price01_min, |
---|
54 | price01_max, |
---|
55 | price02_min, |
---|
56 | price02_max, |
---|
57 | stock_min, |
---|
58 | stock_max, |
---|
59 | stock_unlimited_min, |
---|
60 | stock_unlimited_max, |
---|
61 | category_rank, |
---|
62 | T5.category_id, |
---|
63 | T5.del_flg, |
---|
64 | T5.status, |
---|
65 | T5.name, |
---|
66 | T5.comment1, |
---|
67 | T5.comment2, |
---|
68 | T5.comment3, |
---|
69 | T5.rank, |
---|
70 | T5.main_list_comment, |
---|
71 | T5.main_image, |
---|
72 | T5.main_list_image, |
---|
73 | T5.product_flag, |
---|
74 | T5.deliv_date_id, |
---|
75 | T5.sale_limit, |
---|
76 | T5.point_rate, |
---|
77 | T5.sale_unlimited, |
---|
78 | T5.create_date, |
---|
79 | T5.deliv_fee |
---|
80 | FROM |
---|
81 | ((SELECT T1.product_id, |
---|
82 | T1.del_flg, |
---|
83 | T1.status, |
---|
84 | T1.name, |
---|
85 | T1.comment1, |
---|
86 | T1.comment2, |
---|
87 | T1.comment3, |
---|
88 | T1.main_list_comment, |
---|
89 | T1.main_image, |
---|
90 | T1.main_list_image, |
---|
91 | T1.product_flag, |
---|
92 | T1.deliv_date_id, |
---|
93 | T1.sale_limit, |
---|
94 | T1.point_rate, |
---|
95 | T1.sale_unlimited, |
---|
96 | T1.create_date, |
---|
97 | T1.deliv_fee, |
---|
98 | T2.category_id, |
---|
99 | T1.rank |
---|
100 | FROM dtb_products AS T1 |
---|
101 | LEFT JOIN dtb_product_categories AS T2 |
---|
102 | ON T1.product_id = T2.product_id) AS T3 |
---|
103 | RIGHT JOIN |
---|
104 | (SELECT product_id AS product_id_sub, |
---|
105 | MIN(product_code) AS product_code_min, |
---|
106 | MAX(product_code) AS product_code_max, |
---|
107 | MIN(price01) AS price01_min, |
---|
108 | MAX(price01) AS price01_max, |
---|
109 | MIN(price02) AS price02_min, |
---|
110 | MAX(price02) AS price02_max, |
---|
111 | MIN(stock) AS stock_min, |
---|
112 | MAX(stock) AS stock_max, |
---|
113 | MIN(stock_unlimited) AS stock_unlimited_min, |
---|
114 | MAX(stock_unlimited) AS stock_unlimited_max |
---|
115 | FROM dtb_products_class |
---|
116 | GROUP BY product_id) AS T4 |
---|
117 | ON T3.product_id = T4.product_id_sub) AS T5 |
---|
118 | LEFT JOIN |
---|
119 | (SELECT rank AS category_rank, |
---|
120 | category_id AS sub_category_id |
---|
121 | FROM dtb_category) AS T6 |
---|
122 | ON T5.category_id = T6.sub_category_id; |
---|
123 | |
---|
124 | CREATE VIEW vw_products_allclass_detail AS |
---|
125 | SELECT product_id, |
---|
126 | price01_min, |
---|
127 | price01_max, |
---|
128 | price02_min, |
---|
129 | price02_max, |
---|
130 | stock_min, |
---|
131 | stock_max, |
---|
132 | stock_unlimited_min, |
---|
133 | stock_unlimited_max, |
---|
134 | del_flg, |
---|
135 | status, |
---|
136 | name, |
---|
137 | comment1, |
---|
138 | comment2, |
---|
139 | comment3, |
---|
140 | deliv_fee, |
---|
141 | main_comment, |
---|
142 | main_image, |
---|
143 | main_large_image, |
---|
144 | sub_title1, |
---|
145 | sub_comment1, |
---|
146 | sub_image1, |
---|
147 | sub_large_image1, |
---|
148 | sub_title2, |
---|
149 | sub_comment2, |
---|
150 | sub_image2, |
---|
151 | sub_large_image2, |
---|
152 | sub_title3, |
---|
153 | sub_comment3, |
---|
154 | sub_image3, |
---|
155 | sub_large_image3, |
---|
156 | sub_title4, |
---|
157 | sub_comment4, |
---|
158 | sub_image4, |
---|
159 | sub_large_image4, |
---|
160 | sub_title5, |
---|
161 | sub_comment5, |
---|
162 | sub_image5, |
---|
163 | sub_large_image5, |
---|
164 | product_flag, |
---|
165 | deliv_date_id, |
---|
166 | sale_limit, |
---|
167 | point_rate, |
---|
168 | sale_unlimited, |
---|
169 | file1,file2, |
---|
170 | category_id |
---|
171 | FROM (dtb_products AS T1 |
---|
172 | RIGHT JOIN |
---|
173 | (SELECT |
---|
174 | product_id AS product_id_sub, |
---|
175 | MIN(price01) AS price01_min, |
---|
176 | MAX(price01) AS price01_max, |
---|
177 | MIN(price02) AS price02_min, |
---|
178 | MAX(price02) AS price02_max, |
---|
179 | MIN(stock) AS stock_min, |
---|
180 | MAX(stock) AS stock_max, |
---|
181 | MIN(stock_unlimited) AS stock_unlimited_min, |
---|
182 | MAX(stock_unlimited) AS stock_unlimited_max |
---|
183 | FROM dtb_products_class |
---|
184 | GROUP BY product_id) AS T2 |
---|
185 | ON T1.product_id = T2.product_id_sub) AS T3 |
---|
186 | LEFT JOIN (SELECT rank AS category_rank, |
---|
187 | category_id AS sub_category_id |
---|
188 | FROM dtb_category) AS T4 |
---|
189 | ON T3.category_id = T4.sub_category_id; |
---|
190 | |
---|
191 | CREATE VIEW vw_product_class AS |
---|
192 | SELECT * |
---|
193 | FROM |
---|
194 | (SELECT T3.product_class_id, |
---|
195 | T3.product_id AS product_id_sub, |
---|
196 | classcategory_id1, |
---|
197 | classcategory_id2, |
---|
198 | T3.rank AS rank1, |
---|
199 | T4.rank AS rank2, |
---|
200 | T3.class_id AS class_id1, |
---|
201 | T4.class_id AS class_id2, |
---|
202 | stock, |
---|
203 | price01, |
---|
204 | price02, |
---|
205 | stock_unlimited, |
---|
206 | product_code |
---|
207 | FROM (dtb_products_class AS T1 |
---|
208 | LEFT JOIN dtb_classcategory AS T2 |
---|
209 | ON T1.classcategory_id1 = T2.classcategory_id) AS T3 |
---|
210 | LEFT JOIN dtb_classcategory AS T4 |
---|
211 | ON T3.classcategory_id2 = T4.classcategory_id) AS T5 |
---|
212 | LEFT JOIN dtb_products AS T6 |
---|
213 | ON product_id_sub = T6.product_id; |
---|
214 | |
---|
215 | CREATE VIEW vw_category_count AS |
---|
216 | SELECT T1.category_id, |
---|
217 | T1.category_name, |
---|
218 | T1.parent_category_id, |
---|
219 | T1.level, |
---|
220 | T1.rank, |
---|
221 | T2.product_count |
---|
222 | FROM dtb_category AS T1 |
---|
223 | LEFT JOIN dtb_category_total_count AS T2 |
---|
224 | ON T1.category_id = T2.category_id |
---|
225 | |
---|