| 1 | CREATE 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 | |
---|
| 16 | CREATE 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 | |
---|
| 38 | CREATE 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 | |
---|
| 61 | CREATE 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 | |
---|
| 154 | CREATE 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 | |
---|
| 169 | CREATE 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 | |
---|
| 193 | CREATE 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 | ; |
---|