| 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 | ; |
|---|
