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

Revision 18824, 3.3 KB checked in by eccuore, 11 years ago (diff)

#792(ダウンロード販売機能) vw_download_class削除、product_class_id対応(規格構成変更と並行作業中なので、作業途中の部分有)

  • Property svn:mime-type set to application/x-httpd-php; charset=UTF-8
Line 
1CREATE VIEW vw_products_allclass_detail AS
2    SELECT
3        dtb_products.product_id,
4        dtb_products."name",
5        dtb_products.maker_id,
6        dtb_products.rank,
7        dtb_products.status,
8        dtb_products.comment1,
9        dtb_products.comment2,
10        dtb_products.comment3,
11        dtb_products.comment4,
12        dtb_products.comment5,
13        dtb_products.comment6,
14        dtb_products.note,
15        dtb_products.file1,
16        dtb_products.file2,
17        dtb_products.file3,
18        dtb_products.file4,
19        dtb_products.file5,
20        dtb_products.file6,
21        dtb_products.main_list_comment,
22        dtb_products.main_list_image,
23        dtb_products.main_comment,
24        dtb_products.main_image,
25        dtb_products.main_large_image,
26        dtb_products.sub_title1,
27        dtb_products.sub_comment1,
28        dtb_products.sub_image1,
29        dtb_products.sub_large_image1,
30        dtb_products.sub_title2,
31        dtb_products.sub_comment2,
32        dtb_products.sub_image2,
33        dtb_products.sub_large_image2,
34        dtb_products.sub_title3,
35        dtb_products.sub_comment3,
36        dtb_products.sub_image3,
37        dtb_products.sub_large_image3,
38        dtb_products.sub_title4,
39        dtb_products.sub_comment4,
40        dtb_products.sub_image4,
41        dtb_products.sub_large_image4,
42        dtb_products.sub_title5,
43        dtb_products.sub_comment5,
44        dtb_products.sub_image5,
45        dtb_products.sub_large_image5,
46        dtb_products.sub_title6,
47        dtb_products.sub_comment6,
48        dtb_products.sub_image6,
49        dtb_products.sub_large_image6,
50        dtb_products.del_flg,
51        dtb_products.creator_id,
52        dtb_products.create_date,
53        dtb_products.update_date,
54        dtb_products.deliv_date_id,
55        T4.product_code_min,
56        T4.product_code_max,
57        T4.price01_min,
58        T4.price01_max,
59        T4.price02_min,
60        T4.price02_max,
61        T4.stock_min,
62        T4.stock_max,
63        T4.stock_unlimited_min,
64        T4.stock_unlimited_max,
65        T4.class_count
66    FROM
67        dtb_products
68        LEFT JOIN
69            (
70                SELECT
71                    product_id,
72                    MIN(product_code) AS product_code_min,
73                    MAX(product_code) AS product_code_max,
74                    MIN(price01) AS price01_min,
75                    MAX(price01) AS price01_max,
76                    MIN(price02) AS price02_min,
77                    MAX(price02) AS price02_max,
78                    MIN(stock) AS stock_min,
79                    MAX(stock) AS stock_max,
80                    MIN(stock_unlimited) AS stock_unlimited_min,
81                    MAX(stock_unlimited) AS stock_unlimited_max,
82                    COUNT(*) as class_count
83                FROM dtb_products_class
84                GROUP BY product_id
85            ) AS T4
86            ON dtb_products.product_id = T4.product_id
87;
88
89CREATE VIEW vw_products_allclass AS
90    SELECT
91        alldtl.*,
92        dtb_category.rank AS category_rank,
93        T2.category_id,
94        T2.rank AS product_rank
95    FROM
96        vw_products_allclass_detail AS alldtl
97        LEFT JOIN
98            dtb_product_categories AS T2
99            ON alldtl.product_id = T2.product_id
100        LEFT JOIN
101            dtb_category
102            ON T2.category_id = dtb_category.category_id
103;
Note: See TracBrowser for help on using the repository browser.