source: branches/feature-module-update/html/install/sql/create_view.sql @ 16546

Revision 16546, 7.0 KB checked in by nanasess, 14 years ago (diff)

複数カテゴリ対応

  • 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       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
124CREATE 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
191CREATE 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
215CREATE 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
Note: See TracBrowser for help on using the repository browser.