1 | CREATE 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 | dtb_products.down, |
---|
56 | dtb_products.down_filename, |
---|
57 | dtb_products.down_realfilename, |
---|
58 | T4.product_code_min, |
---|
59 | T4.product_code_max, |
---|
60 | T4.price01_min, |
---|
61 | T4.price01_max, |
---|
62 | T4.price02_min, |
---|
63 | T4.price02_max, |
---|
64 | T4.stock_min, |
---|
65 | T4.stock_max, |
---|
66 | T4.stock_unlimited_min, |
---|
67 | T4.stock_unlimited_max, |
---|
68 | T4.class_count |
---|
69 | FROM |
---|
70 | dtb_products |
---|
71 | LEFT JOIN |
---|
72 | ( |
---|
73 | SELECT |
---|
74 | product_id, |
---|
75 | MIN(product_code) AS product_code_min, |
---|
76 | MAX(product_code) AS product_code_max, |
---|
77 | MIN(price01) AS price01_min, |
---|
78 | MAX(price01) AS price01_max, |
---|
79 | MIN(price02) AS price02_min, |
---|
80 | MAX(price02) AS price02_max, |
---|
81 | MIN(stock) AS stock_min, |
---|
82 | MAX(stock) AS stock_max, |
---|
83 | MIN(stock_unlimited) AS stock_unlimited_min, |
---|
84 | MAX(stock_unlimited) AS stock_unlimited_max, |
---|
85 | COUNT(*) as class_count |
---|
86 | FROM dtb_products_class |
---|
87 | GROUP BY product_id |
---|
88 | ) AS T4 |
---|
89 | ON dtb_products.product_id = T4.product_id |
---|
90 | ; |
---|
91 | |
---|
92 | CREATE VIEW vw_products_allclass AS |
---|
93 | SELECT |
---|
94 | alldtl.*, |
---|
95 | dtb_category.rank AS category_rank, |
---|
96 | T2.category_id, |
---|
97 | T2.rank AS product_rank |
---|
98 | FROM |
---|
99 | vw_products_allclass_detail AS alldtl |
---|
100 | LEFT JOIN |
---|
101 | dtb_product_categories AS T2 |
---|
102 | ON alldtl.product_id = T2.product_id |
---|
103 | LEFT JOIN |
---|
104 | dtb_category |
---|
105 | ON T2.category_id = dtb_category.category_id |
---|
106 | ; |
---|