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

Revision 18777, 6.5 KB checked in by eccuore, 10 years ago (diff)

#792(ダウンロード販売機能) 機能追加

  • 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        dtb_products.down,
120        dtb_products.down_filename,
121        dtb_products.down_realfilename,
122        T4.product_code_min,
123        T4.product_code_max,
124        T4.price01_min,
125        T4.price01_max,
126        T4.price02_min,
127        T4.price02_max,
128        T4.stock_min,
129        T4.stock_max,
130        T4.stock_unlimited_min,
131        T4.stock_unlimited_max,
132        T4.class_count
133    FROM
134        dtb_products
135        LEFT JOIN
136            (
137                SELECT
138                    product_id,
139                    MIN(product_code) AS product_code_min,
140                    MAX(product_code) AS product_code_max,
141                    MIN(price01) AS price01_min,
142                    MAX(price01) AS price01_max,
143                    MIN(price02) AS price02_min,
144                    MAX(price02) AS price02_max,
145                    MIN(stock) AS stock_min,
146                    MAX(stock) AS stock_max,
147                    MIN(stock_unlimited) AS stock_unlimited_min,
148                    MAX(stock_unlimited) AS stock_unlimited_max,
149                    COUNT(*) as class_count
150                FROM dtb_products_class
151                GROUP BY product_id
152            ) AS T4
153            ON dtb_products.product_id = T4.product_id
154;
155
156CREATE VIEW vw_products_allclass AS
157    SELECT
158        alldtl.*,
159        dtb_category.rank AS category_rank,
160        T2.category_id,
161        T2.rank AS product_rank
162    FROM
163        vw_products_allclass_detail AS alldtl
164        LEFT JOIN
165            dtb_product_categories AS T2
166            ON alldtl.product_id = T2.product_id
167        LEFT JOIN
168            dtb_category
169            ON T2.category_id = dtb_category.category_id
170;
171
172CREATE VIEW vw_product_class AS
173     SELECT *
174       FROM
175      (SELECT T3.product_class_id,
176              T3.product_id AS product_id_sub,
177              classcategory_id1,
178              classcategory_id2,
179              T3.rank AS rank1,
180              T4.rank AS rank2,
181              T3.class_id AS class_id1,
182              T4.class_id AS class_id2,
183              stock,
184              price01,
185              price02,
186              stock_unlimited,
187              product_code
188         FROM (dtb_products_class AS T1
189    LEFT JOIN dtb_classcategory AS T2
190           ON T1.classcategory_id1 = T2.classcategory_id) AS T3
191  LEFT JOIN dtb_classcategory AS T4
192         ON T3.classcategory_id2 = T4.classcategory_id) AS T5
193  LEFT JOIN dtb_products AS T6
194         ON product_id_sub = T6.product_id;
195
196CREATE VIEW vw_category_count AS
197     SELECT T1.category_id,
198            T1.category_name,
199            T1.parent_category_id,
200            T1.level,
201            T1.rank,
202            T2.product_count
203       FROM dtb_category AS T1
204  LEFT JOIN dtb_category_total_count AS T2
205         ON T1.category_id = T2.category_id
206;
207
208CREATE VIEW vw_download_class as
209     SELECT
210          p.product_id AS product_id,
211          p.down_realfilename AS down_realfilename,
212          p.down_filename AS down_filename,
213          od.order_id AS order_id,
214          o.customer_id AS customer_id,
215          o.commit_date AS commit_date,
216          o.status AS status
217     FROM
218          dtb_products p,
219          dtb_order_detail od,
220          dtb_order o
221     WHERE
222          p.product_id = od.product_id AND
223          od.order_id = o.order_id;
Note: See TracBrowser for help on using the repository browser.