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

Revision 17549, 6.0 KB checked in by Seasoft, 14 years ago (diff)

・VIEWを改訂。簡素化と高速化を目論む。(従来互換を考慮していますが、影響範囲が大きいので不具合もあるかと思います。また、MySQL v4系は、環境が無いため未テストです。バグレポートをお願いします。)
r17509 の不具合対応。
r17548 の応急処置を正式に対応。
・[検索結果をすべて削除]でエラーが発生する不具合を修正。

  • 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.sale_unlimited,
68        dtb_products.maker_id,
69        dtb_products.rank,
70        dtb_products.status,
71        dtb_products.product_flag,
72        dtb_products.point_rate,
73        dtb_products.comment1,
74        dtb_products.comment2,
75        dtb_products.comment3,
76        dtb_products.comment4,
77        dtb_products.comment5,
78        dtb_products.comment6,
79        dtb_products.note,
80        dtb_products.file1,
81        dtb_products.file2,
82        dtb_products.file3,
83        dtb_products.file4,
84        dtb_products.file5,
85        dtb_products.file6,
86        dtb_products.main_list_comment,
87        dtb_products.main_list_image,
88        dtb_products.main_comment,
89        dtb_products.main_image,
90        dtb_products.main_large_image,
91        dtb_products.sub_title1,
92        dtb_products.sub_comment1,
93        dtb_products.sub_image1,
94        dtb_products.sub_large_image1,
95        dtb_products.sub_title2,
96        dtb_products.sub_comment2,
97        dtb_products.sub_image2,
98        dtb_products.sub_large_image2,
99        dtb_products.sub_title3,
100        dtb_products.sub_comment3,
101        dtb_products.sub_image3,
102        dtb_products.sub_large_image3,
103        dtb_products.sub_title4,
104        dtb_products.sub_comment4,
105        dtb_products.sub_image4,
106        dtb_products.sub_large_image4,
107        dtb_products.sub_title5,
108        dtb_products.sub_comment5,
109        dtb_products.sub_image5,
110        dtb_products.sub_large_image5,
111        dtb_products.sub_title6,
112        dtb_products.sub_comment6,
113        dtb_products.sub_image6,
114        dtb_products.sub_large_image6,
115        dtb_products.del_flg,
116        dtb_products.creator_id,
117        dtb_products.create_date,
118        dtb_products.update_date,
119        dtb_products.deliv_date_id,
120        T4.product_code_min,
121        T4.product_code_max,
122        T4.price01_min,
123        T4.price01_max,
124        T4.price02_min,
125        T4.price02_max,
126        T4.stock_min,
127        T4.stock_max,
128        T4.stock_unlimited_min,
129        T4.stock_unlimited_max,
130        T4.class_count
131    FROM
132        dtb_products
133        LEFT JOIN
134            (
135                SELECT
136                    product_id,
137                    MIN(product_code) AS product_code_min,
138                    MAX(product_code) AS product_code_max,
139                    MIN(price01) AS price01_min,
140                    MAX(price01) AS price01_max,
141                    MIN(price02) AS price02_min,
142                    MAX(price02) AS price02_max,
143                    MIN(stock) AS stock_min,
144                    MAX(stock) AS stock_max,
145                    MIN(stock_unlimited) AS stock_unlimited_min,
146                    MAX(stock_unlimited) AS stock_unlimited_max,
147                    COUNT(*) as class_count
148                FROM dtb_products_class
149                GROUP BY product_id
150            ) AS T4
151            ON dtb_products.product_id = T4.product_id
152;
153
154CREATE VIEW vw_products_allclass AS
155    SELECT
156        alldtl.*,
157        dtb_category.rank AS category_rank,
158        T2.category_id
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.