source: branches/comu-ver2/html/install/sql/create_view.sql @ 17537

Revision 17537, 7.0 KB checked in by Seasoft, 14 years ago (diff)

管理画面 - 商品管理 - 商品マスタ
複数の規格を有する商品の表示を改善。
(注意) view の再作成を要します。

  • Property svn:mime-type set to application/x-httpd-php; charset=UTF-8
Line 
1CREATE 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
13CREATE 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
32CREATE 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
49CREATE 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            T5.update_date
81       FROM
82             ((SELECT T1.product_id,
83                      T1.del_flg,
84                      T1.status,
85                      T1.name,
86                      T1.comment1,
87                      T1.comment2,
88                      T1.comment3,
89                      T1.main_list_comment,
90                      T1.main_image,
91                      T1.main_list_image,
92                      T1.product_flag,
93                      T1.deliv_date_id,
94                      T1.sale_limit,
95                      T1.point_rate,
96                      T1.sale_unlimited,
97                      T1.create_date,
98                      T1.deliv_fee,
99                      T2.category_id,
100                      T1.rank,
101                      T1.update_date
102                 FROM dtb_products AS T1
103            LEFT JOIN dtb_product_categories AS T2
104                   ON T1.product_id = T2.product_id) AS T3
105    RIGHT JOIN
106          (SELECT product_id AS product_id_sub,
107                  MIN(product_code) AS product_code_min,
108                  MAX(product_code) AS product_code_max,
109                  MIN(price01) AS price01_min,
110                  MAX(price01) AS price01_max,
111                  MIN(price02) AS price02_min,
112                  MAX(price02) AS price02_max,
113                  MIN(stock) AS stock_min,
114                  MAX(stock) AS stock_max,
115                  MIN(stock_unlimited) AS stock_unlimited_min,
116                  MAX(stock_unlimited) AS stock_unlimited_max
117             FROM dtb_products_class
118         GROUP BY product_id) AS T4
119               ON T3.product_id = T4.product_id_sub) AS T5
120    LEFT JOIN
121        (SELECT rank AS category_rank,
122                category_id AS sub_category_id
123           FROM dtb_category) AS T6
124          ON T5.category_id = T6.sub_category_id;
125
126CREATE VIEW vw_products_allclass_detail AS
127     SELECT product_id,
128            price01_min,
129            price01_max,
130            price02_min,
131            price02_max,
132            stock_min,
133            stock_max,
134            stock_unlimited_min,
135            stock_unlimited_max,
136            del_flg,
137            status,
138            name,
139            comment1,
140            comment2,
141            comment3,
142            deliv_fee,
143            main_comment,
144            main_image,
145            main_large_image,
146            sub_title1,
147            sub_comment1,
148            sub_image1,
149            sub_large_image1,
150            sub_title2,
151            sub_comment2,
152            sub_image2,
153            sub_large_image2,
154            sub_title3,
155            sub_comment3,
156            sub_image3,
157            sub_large_image3,
158            sub_title4,
159            sub_comment4,
160            sub_image4,
161            sub_large_image4,
162            sub_title5,
163            sub_comment5,
164            sub_image5,
165            sub_large_image5,
166            product_flag,
167            deliv_date_id,
168            sale_limit,
169            point_rate,
170            sale_unlimited,
171            file1,file2,
172            category_id
173      FROM (dtb_products AS T1
174  RIGHT JOIN
175     (SELECT
176             product_id AS product_id_sub,
177             MIN(price01) AS price01_min,
178             MAX(price01) AS price01_max,
179             MIN(price02) AS price02_min,
180             MAX(price02) AS price02_max,
181             MIN(stock) AS stock_min,
182             MAX(stock) AS stock_max,
183             MIN(stock_unlimited) AS stock_unlimited_min,
184             MAX(stock_unlimited) AS stock_unlimited_max
185        FROM dtb_products_class
186    GROUP BY product_id) AS T2
187          ON T1.product_id = T2.product_id_sub) AS T3
188  LEFT JOIN (SELECT rank AS category_rank,
189                    category_id AS sub_category_id
190               FROM dtb_category) AS T4
191         ON T3.category_id = T4.sub_category_id;
192
193CREATE VIEW vw_product_class AS
194     SELECT *
195       FROM
196      (SELECT T3.product_class_id,
197              T3.product_id AS product_id_sub,
198              classcategory_id1,
199              classcategory_id2,
200              T3.rank AS rank1,
201              T4.rank AS rank2,
202              T3.class_id AS class_id1,
203              T4.class_id AS class_id2,
204              stock,
205              price01,
206              price02,
207              stock_unlimited,
208              product_code
209         FROM (dtb_products_class AS T1
210    LEFT JOIN dtb_classcategory AS T2
211           ON T1.classcategory_id1 = T2.classcategory_id) AS T3
212  LEFT JOIN dtb_classcategory AS T4
213         ON T3.classcategory_id2 = T4.classcategory_id) AS T5
214  LEFT JOIN dtb_products AS T6
215         ON product_id_sub = T6.product_id;
216
217CREATE VIEW vw_category_count AS
218     SELECT T1.category_id,
219            T1.category_name,
220            T1.parent_category_id,
221            T1.level,
222            T1.rank,
223            T2.product_count
224       FROM dtb_category AS T1
225  LEFT JOIN dtb_category_total_count AS T2
226         ON T1.category_id = T2.category_id
227
Note: See TracBrowser for help on using the repository browser.