source: branches/version-2_5/html/install/sql/create_view.sql @ 18275

Revision 18275, 5.9 KB checked in by Seasoft, 13 years ago (diff)
  • #540(LC_Page_Shopping_Confirm#process 「カート内の商品の売り切れチェック」が動作していない)を改修。
  • #544(サンプルデータで在庫制限が異常動作)を改修。
  • #534(dtb_products.sale_unlimited は不要)を改善。
  • Property svn:mime-type set to application/x-httpd-php; charset=UTF-8
Line 
1CREATE VIEW vw_cross_class as
2    SELECT
3        T1.class_id AS class_id1,
4        T2.class_id AS class_id2,
5        T1.classcategory_id AS classcategory_id1,
6        T2.classcategory_id AS classcategory_id2,
7        T1.name AS name1,
8        T2.name AS name2,
9        T1.rank AS rank1,
10        T2.rank AS rank2
11    FROM
12        dtb_classcategory AS T1,
13        dtb_classcategory AS T2
14;
15
16CREATE VIEW vw_cross_products_class AS
17    SELECT
18        T1.class_id1,
19        T1.class_id2,
20        T1.classcategory_id1,
21        T1.classcategory_id2,
22        T2.product_id,
23        T1.name1,
24        T1.name2,
25        T2.product_code,
26        T2.stock,
27        T2.price01,
28        T2.price02,
29        T1.rank1,
30        T1.rank2
31    FROM
32        vw_cross_class AS T1
33        LEFT JOIN dtb_products_class AS T2
34            ON T1.classcategory_id1 = T2.classcategory_id1
35            AND T1.classcategory_id2 = T2.classcategory_id2
36;
37
38CREATE VIEW vw_products_nonclass AS
39    SELECT *
40    FROM
41        dtb_products AS T1
42        LEFT JOIN
43        (
44            SELECT
45                product_id AS product_id_sub,
46                product_code,
47                price01,
48                price02,
49                stock,
50                stock_unlimited,
51                classcategory_id1,
52                classcategory_id2
53            FROM dtb_products_class
54            WHERE
55                classcategory_id1 = 0
56                AND classcategory_id2 = 0
57        ) AS T2
58        ON T1.product_id = T2.product_id_sub
59;
60
61CREATE VIEW vw_products_allclass_detail AS
62    SELECT
63        dtb_products.product_id,
64        dtb_products."name",
65        dtb_products.deliv_fee,
66        dtb_products.sale_limit,
67        dtb_products.maker_id,
68        dtb_products.rank,
69        dtb_products.status,
70        dtb_products.product_flag,
71        dtb_products.point_rate,
72        dtb_products.comment1,
73        dtb_products.comment2,
74        dtb_products.comment3,
75        dtb_products.comment4,
76        dtb_products.comment5,
77        dtb_products.comment6,
78        dtb_products.note,
79        dtb_products.file1,
80        dtb_products.file2,
81        dtb_products.file3,
82        dtb_products.file4,
83        dtb_products.file5,
84        dtb_products.file6,
85        dtb_products.main_list_comment,
86        dtb_products.main_list_image,
87        dtb_products.main_comment,
88        dtb_products.main_image,
89        dtb_products.main_large_image,
90        dtb_products.sub_title1,
91        dtb_products.sub_comment1,
92        dtb_products.sub_image1,
93        dtb_products.sub_large_image1,
94        dtb_products.sub_title2,
95        dtb_products.sub_comment2,
96        dtb_products.sub_image2,
97        dtb_products.sub_large_image2,
98        dtb_products.sub_title3,
99        dtb_products.sub_comment3,
100        dtb_products.sub_image3,
101        dtb_products.sub_large_image3,
102        dtb_products.sub_title4,
103        dtb_products.sub_comment4,
104        dtb_products.sub_image4,
105        dtb_products.sub_large_image4,
106        dtb_products.sub_title5,
107        dtb_products.sub_comment5,
108        dtb_products.sub_image5,
109        dtb_products.sub_large_image5,
110        dtb_products.sub_title6,
111        dtb_products.sub_comment6,
112        dtb_products.sub_image6,
113        dtb_products.sub_large_image6,
114        dtb_products.del_flg,
115        dtb_products.creator_id,
116        dtb_products.create_date,
117        dtb_products.update_date,
118        dtb_products.deliv_date_id,
119        T4.product_code_min,
120        T4.product_code_max,
121        T4.price01_min,
122        T4.price01_max,
123        T4.price02_min,
124        T4.price02_max,
125        T4.stock_min,
126        T4.stock_max,
127        T4.stock_unlimited_min,
128        T4.stock_unlimited_max,
129        T4.class_count
130    FROM
131        dtb_products
132        LEFT JOIN
133            (
134                SELECT
135                    product_id,
136                    MIN(product_code) AS product_code_min,
137                    MAX(product_code) AS product_code_max,
138                    MIN(price01) AS price01_min,
139                    MAX(price01) AS price01_max,
140                    MIN(price02) AS price02_min,
141                    MAX(price02) AS price02_max,
142                    MIN(stock) AS stock_min,
143                    MAX(stock) AS stock_max,
144                    MIN(stock_unlimited) AS stock_unlimited_min,
145                    MAX(stock_unlimited) AS stock_unlimited_max,
146                    COUNT(*) as class_count
147                FROM dtb_products_class
148                GROUP BY product_id
149            ) AS T4
150            ON dtb_products.product_id = T4.product_id
151;
152
153CREATE VIEW vw_products_allclass AS
154    SELECT
155        alldtl.*,
156        dtb_category.rank AS category_rank,
157        T2.category_id,
158        T2.rank AS product_rank
159    FROM
160        vw_products_allclass_detail AS alldtl
161        LEFT JOIN
162            dtb_product_categories AS T2
163            ON alldtl.product_id = T2.product_id
164        LEFT JOIN
165            dtb_category
166            ON T2.category_id = dtb_category.category_id
167;
168
169CREATE VIEW vw_product_class AS
170     SELECT *
171       FROM
172      (SELECT T3.product_class_id,
173              T3.product_id AS product_id_sub,
174              classcategory_id1,
175              classcategory_id2,
176              T3.rank AS rank1,
177              T4.rank AS rank2,
178              T3.class_id AS class_id1,
179              T4.class_id AS class_id2,
180              stock,
181              price01,
182              price02,
183              stock_unlimited,
184              product_code
185         FROM (dtb_products_class AS T1
186    LEFT JOIN dtb_classcategory AS T2
187           ON T1.classcategory_id1 = T2.classcategory_id) AS T3
188  LEFT JOIN dtb_classcategory AS T4
189         ON T3.classcategory_id2 = T4.classcategory_id) AS T5
190  LEFT JOIN dtb_products AS T6
191         ON product_id_sub = T6.product_id;
192
193CREATE VIEW vw_category_count AS
194     SELECT T1.category_id,
195            T1.category_name,
196            T1.parent_category_id,
197            T1.level,
198            T1.rank,
199            T2.product_count
200       FROM dtb_category AS T1
201  LEFT JOIN dtb_category_total_count AS T2
202         ON T1.category_id = T2.category_id
203;
Note: See TracBrowser for help on using the repository browser.