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_detail AS |
---|
50 | SELECT |
---|
51 | dtb_products.product_id, |
---|
52 | dtb_products."name", |
---|
53 | dtb_products.deliv_fee, |
---|
54 | dtb_products.sale_limit, |
---|
55 | dtb_products.rank, |
---|
56 | dtb_products.status, |
---|
57 | dtb_products.product_flag, |
---|
58 | dtb_products.point_rate, |
---|
59 | dtb_products.sale_unlimited, |
---|
60 | dtb_products.comment1, |
---|
61 | dtb_products.comment2, |
---|
62 | dtb_products.comment3, |
---|
63 | dtb_products.comment4, |
---|
64 | dtb_products.comment5, |
---|
65 | dtb_products.comment6, |
---|
66 | dtb_products.note, |
---|
67 | dtb_products.file1, |
---|
68 | dtb_products.file2, |
---|
69 | dtb_products.file3, |
---|
70 | dtb_products.file4, |
---|
71 | dtb_products.file5, |
---|
72 | dtb_products.file6, |
---|
73 | dtb_products.main_list_comment, |
---|
74 | dtb_products.main_list_image, |
---|
75 | dtb_products.main_comment, |
---|
76 | dtb_products.main_image, |
---|
77 | dtb_products.main_large_image, |
---|
78 | dtb_products.sub_title1, |
---|
79 | dtb_products.sub_comment1, |
---|
80 | dtb_products.sub_image1, |
---|
81 | dtb_products.sub_large_image1, |
---|
82 | dtb_products.sub_title2, |
---|
83 | dtb_products.sub_comment2, |
---|
84 | dtb_products.sub_image2, |
---|
85 | dtb_products.sub_large_image2, |
---|
86 | dtb_products.sub_title3, |
---|
87 | dtb_products.sub_comment3, |
---|
88 | dtb_products.sub_image3, |
---|
89 | dtb_products.sub_large_image3, |
---|
90 | dtb_products.sub_title4, |
---|
91 | dtb_products.sub_comment4, |
---|
92 | dtb_products.sub_image4, |
---|
93 | dtb_products.sub_large_image4, |
---|
94 | dtb_products.sub_title5, |
---|
95 | dtb_products.sub_comment5, |
---|
96 | dtb_products.sub_image5, |
---|
97 | dtb_products.sub_large_image5, |
---|
98 | dtb_products.sub_title6, |
---|
99 | dtb_products.sub_comment6, |
---|
100 | dtb_products.sub_image6, |
---|
101 | dtb_products.sub_large_image6, |
---|
102 | dtb_products.del_flg, |
---|
103 | dtb_products.creator_id, |
---|
104 | dtb_products.create_date, |
---|
105 | dtb_products.update_date, |
---|
106 | dtb_products.deliv_date_id, |
---|
107 | T4.product_code_min, |
---|
108 | T4.product_code_max, |
---|
109 | T4.price01_min, |
---|
110 | T4.price01_max, |
---|
111 | T4.price02_min, |
---|
112 | T4.price02_max, |
---|
113 | T4.stock_min, |
---|
114 | T4.stock_max, |
---|
115 | T4.stock_unlimited_min, |
---|
116 | T4.stock_unlimited_max, |
---|
117 | T4.class_count |
---|
118 | FROM |
---|
119 | dtb_products |
---|
120 | LEFT JOIN |
---|
121 | ( |
---|
122 | SELECT |
---|
123 | product_id, |
---|
124 | MIN(product_code) AS product_code_min, |
---|
125 | MAX(product_code) AS product_code_max, |
---|
126 | MIN(price01) AS price01_min, |
---|
127 | MAX(price01) AS price01_max, |
---|
128 | MIN(price02) AS price02_min, |
---|
129 | MAX(price02) AS price02_max, |
---|
130 | MIN(stock) AS stock_min, |
---|
131 | MAX(stock) AS stock_max, |
---|
132 | MIN(stock_unlimited) AS stock_unlimited_min, |
---|
133 | MAX(stock_unlimited) AS stock_unlimited_max, |
---|
134 | COUNT(*) as class_count |
---|
135 | FROM dtb_products_class |
---|
136 | GROUP BY product_id |
---|
137 | ) AS T4 |
---|
138 | ON dtb_products.product_id = T4.product_id |
---|
139 | ; |
---|
140 | |
---|
141 | CREATE VIEW vw_products_allclass AS |
---|
142 | SELECT |
---|
143 | alldtl.*, |
---|
144 | dtb_category.rank AS category_rank, |
---|
145 | T2.category_id, |
---|
146 | T2.rank AS product_rank |
---|
147 | FROM |
---|
148 | vw_products_allclass_detail AS alldtl |
---|
149 | LEFT JOIN |
---|
150 | dtb_product_categories AS T2 |
---|
151 | ON alldtl.product_id = T2.product_id |
---|
152 | LEFT JOIN |
---|
153 | dtb_category |
---|
154 | ON T2.category_id = dtb_category.category_id |
---|
155 | ; |
---|
156 | |
---|
157 | CREATE VIEW vw_product_class AS |
---|
158 | SELECT * |
---|
159 | FROM |
---|
160 | (SELECT T3.product_class_id, |
---|
161 | T3.product_id AS product_id_sub, |
---|
162 | classcategory_id1, |
---|
163 | classcategory_id2, |
---|
164 | T3.rank AS rank1, |
---|
165 | T4.rank AS rank2, |
---|
166 | T3.class_id AS class_id1, |
---|
167 | T4.class_id AS class_id2, |
---|
168 | stock, |
---|
169 | price01, |
---|
170 | price02, |
---|
171 | stock_unlimited, |
---|
172 | product_code |
---|
173 | FROM (dtb_products_class AS T1 |
---|
174 | LEFT JOIN dtb_classcategory AS T2 |
---|
175 | ON T1.classcategory_id1 = T2.classcategory_id) AS T3 |
---|
176 | LEFT JOIN dtb_classcategory AS T4 |
---|
177 | ON T3.classcategory_id2 = T4.classcategory_id) AS T5 |
---|
178 | LEFT JOIN dtb_products AS T6 |
---|
179 | ON product_id_sub = T6.product_id; |
---|
180 | |
---|
181 | CREATE VIEW vw_category_count AS |
---|
182 | SELECT T1.category_id, |
---|
183 | T1.category_name, |
---|
184 | T1.parent_category_id, |
---|
185 | T1.level, |
---|
186 | T1.rank, |
---|
187 | T2.product_count |
---|
188 | FROM dtb_category AS T1 |
---|
189 | LEFT JOIN dtb_category_total_count AS T2 |
---|
190 | ON T1.category_id = T2.category_id |
---|
191 | |
---|