| 1 | CREATE VIEW vw_cross_class as |
---|
| 2 | SELECT T1.class_id AS class_id1, |
---|
| 3 | T2.class_id AS class_id2, |
---|
| 4 | T1.classcategory_id AS classcategory_id1, |
---|
| 5 | T2.classcategory_id AS classcategory_id2, |
---|
| 6 | T1.name AS name1, |
---|
| 7 | T2.name AS name2, |
---|
| 8 | T1.rank AS rank1, |
---|
| 9 | T2.rank AS rank2 |
---|
| 10 | FROM dtb_classcategory AS T1, |
---|
| 11 | dtb_classcategory AS T2; |
---|
| 12 | |
---|
| 13 | CREATE VIEW vw_cross_products_class AS |
---|
| 14 | SELECT T1.class_id1, |
---|
| 15 | T1.class_id2, |
---|
| 16 | T1.classcategory_id1, |
---|
| 17 | T1.classcategory_id2, |
---|
| 18 | T2.product_id, |
---|
| 19 | T1.name1, |
---|
| 20 | T1.name2, |
---|
| 21 | T2.product_code, |
---|
| 22 | T2.stock, |
---|
| 23 | T2.price01, |
---|
| 24 | T2.price02, |
---|
| 25 | T1.rank1, |
---|
| 26 | T1.rank2 |
---|
| 27 | FROM vw_cross_class AS T1 |
---|
| 28 | LEFT JOIN dtb_products_class AS T2 |
---|
| 29 | ON T1.classcategory_id1 = T2.classcategory_id1 |
---|
| 30 | AND T1.classcategory_id2 = T2.classcategory_id2; |
---|
| 31 | |
---|
| 32 | CREATE VIEW vw_products_nonclass AS |
---|
| 33 | SELECT * |
---|
| 34 | FROM dtb_products AS T1 LEFT JOIN |
---|
| 35 | (SELECT |
---|
| 36 | product_id AS product_id_sub, |
---|
| 37 | product_code, |
---|
| 38 | price01, |
---|
| 39 | price02, |
---|
| 40 | stock, |
---|
| 41 | stock_unlimited, |
---|
| 42 | classcategory_id1, |
---|
| 43 | classcategory_id2 |
---|
| 44 | FROM dtb_products_class |
---|
| 45 | WHERE classcategory_id1 = 0 |
---|
| 46 | AND classcategory_id2 = 0) AS T2 |
---|
| 47 | ON T1.product_id = T2.product_id_sub; |
---|
| 48 | |
---|
| 49 | CREATE VIEW vw_products_allclass AS |
---|
| 50 | SELECT T5.product_id, |
---|
| 51 | product_code_min, |
---|
| 52 | product_code_max, |
---|
| 53 | price01_min, |
---|
| 54 | price01_max, |
---|
| 55 | price02_min, |
---|
| 56 | price02_max, |
---|
| 57 | stock_min, |
---|
| 58 | stock_max, |
---|
| 59 | stock_unlimited_min, |
---|
| 60 | stock_unlimited_max, |
---|
| 61 | category_rank, |
---|
| 62 | T5.category_id, |
---|
| 63 | T5.del_flg, |
---|
| 64 | T5.status, |
---|
| 65 | T5.name, |
---|
| 66 | T5.comment1, |
---|
| 67 | T5.comment2, |
---|
| 68 | T5.comment3, |
---|
| 69 | T5.rank, |
---|
| 70 | T5.main_list_comment, |
---|
| 71 | T5.main_image, |
---|
| 72 | T5.main_list_image, |
---|
| 73 | T5.product_flag, |
---|
| 74 | T5.deliv_date_id, |
---|
| 75 | T5.sale_limit, |
---|
| 76 | T5.point_rate, |
---|
| 77 | T5.sale_unlimited, |
---|
| 78 | T5.create_date, |
---|
| 79 | T5.deliv_fee, |
---|
| 80 | T5.update_date |
---|
| 81 | FROM |
---|
| 82 | ((SELECT T1.product_id, |
---|
| 83 | T1.del_flg, |
---|
| 84 | T1.status, |
---|
| 85 | T1.name, |
---|
| 86 | T1.comment1, |
---|
| 87 | T1.comment2, |
---|
| 88 | T1.comment3, |
---|
| 89 | T1.main_list_comment, |
---|
| 90 | T1.main_image, |
---|
| 91 | T1.main_list_image, |
---|
| 92 | T1.product_flag, |
---|
| 93 | T1.deliv_date_id, |
---|
| 94 | T1.sale_limit, |
---|
| 95 | T1.point_rate, |
---|
| 96 | T1.sale_unlimited, |
---|
| 97 | T1.create_date, |
---|
| 98 | T1.deliv_fee, |
---|
| 99 | T2.category_id, |
---|
| 100 | T1.rank, |
---|
| 101 | T1.update_date |
---|
| 102 | FROM dtb_products AS T1 |
---|
| 103 | LEFT JOIN dtb_product_categories AS T2 |
---|
| 104 | ON T1.product_id = T2.product_id) AS T3 |
---|
| 105 | RIGHT JOIN |
---|
| 106 | (SELECT product_id AS product_id_sub, |
---|
| 107 | MIN(product_code) AS product_code_min, |
---|
| 108 | MAX(product_code) AS product_code_max, |
---|
| 109 | MIN(price01) AS price01_min, |
---|
| 110 | MAX(price01) AS price01_max, |
---|
| 111 | MIN(price02) AS price02_min, |
---|
| 112 | MAX(price02) AS price02_max, |
---|
| 113 | MIN(stock) AS stock_min, |
---|
| 114 | MAX(stock) AS stock_max, |
---|
| 115 | MIN(stock_unlimited) AS stock_unlimited_min, |
---|
| 116 | MAX(stock_unlimited) AS stock_unlimited_max |
---|
| 117 | FROM dtb_products_class |
---|
| 118 | GROUP BY product_id) AS T4 |
---|
| 119 | ON T3.product_id = T4.product_id_sub) AS T5 |
---|
| 120 | LEFT JOIN |
---|
| 121 | (SELECT rank AS category_rank, |
---|
| 122 | category_id AS sub_category_id |
---|
| 123 | FROM dtb_category) AS T6 |
---|
| 124 | ON T5.category_id = T6.sub_category_id; |
---|
| 125 | |
---|
| 126 | CREATE VIEW vw_products_allclass_detail AS |
---|
| 127 | SELECT product_id, |
---|
| 128 | price01_min, |
---|
| 129 | price01_max, |
---|
| 130 | price02_min, |
---|
| 131 | price02_max, |
---|
| 132 | stock_min, |
---|
| 133 | stock_max, |
---|
| 134 | stock_unlimited_min, |
---|
| 135 | stock_unlimited_max, |
---|
| 136 | del_flg, |
---|
| 137 | status, |
---|
| 138 | name, |
---|
| 139 | comment1, |
---|
| 140 | comment2, |
---|
| 141 | comment3, |
---|
| 142 | deliv_fee, |
---|
| 143 | main_comment, |
---|
| 144 | main_image, |
---|
| 145 | main_large_image, |
---|
| 146 | sub_title1, |
---|
| 147 | sub_comment1, |
---|
| 148 | sub_image1, |
---|
| 149 | sub_large_image1, |
---|
| 150 | sub_title2, |
---|
| 151 | sub_comment2, |
---|
| 152 | sub_image2, |
---|
| 153 | sub_large_image2, |
---|
| 154 | sub_title3, |
---|
| 155 | sub_comment3, |
---|
| 156 | sub_image3, |
---|
| 157 | sub_large_image3, |
---|
| 158 | sub_title4, |
---|
| 159 | sub_comment4, |
---|
| 160 | sub_image4, |
---|
| 161 | sub_large_image4, |
---|
| 162 | sub_title5, |
---|
| 163 | sub_comment5, |
---|
| 164 | sub_image5, |
---|
| 165 | sub_large_image5, |
---|
| 166 | product_flag, |
---|
| 167 | deliv_date_id, |
---|
| 168 | sale_limit, |
---|
| 169 | point_rate, |
---|
| 170 | sale_unlimited, |
---|
| 171 | file1,file2, |
---|
| 172 | category_id |
---|
| 173 | FROM (dtb_products AS T1 |
---|
| 174 | RIGHT JOIN |
---|
| 175 | (SELECT |
---|
| 176 | product_id AS product_id_sub, |
---|
| 177 | MIN(price01) AS price01_min, |
---|
| 178 | MAX(price01) AS price01_max, |
---|
| 179 | MIN(price02) AS price02_min, |
---|
| 180 | MAX(price02) AS price02_max, |
---|
| 181 | MIN(stock) AS stock_min, |
---|
| 182 | MAX(stock) AS stock_max, |
---|
| 183 | MIN(stock_unlimited) AS stock_unlimited_min, |
---|
| 184 | MAX(stock_unlimited) AS stock_unlimited_max |
---|
| 185 | FROM dtb_products_class |
---|
| 186 | GROUP BY product_id) AS T2 |
---|
| 187 | ON T1.product_id = T2.product_id_sub) AS T3 |
---|
| 188 | LEFT JOIN (SELECT rank AS category_rank, |
---|
| 189 | category_id AS sub_category_id |
---|
| 190 | FROM dtb_category) AS T4 |
---|
| 191 | ON T3.category_id = T4.sub_category_id; |
---|
| 192 | |
---|
| 193 | CREATE VIEW vw_product_class AS |
---|
| 194 | SELECT * |
---|
| 195 | FROM |
---|
| 196 | (SELECT T3.product_class_id, |
---|
| 197 | T3.product_id AS product_id_sub, |
---|
| 198 | classcategory_id1, |
---|
| 199 | classcategory_id2, |
---|
| 200 | T3.rank AS rank1, |
---|
| 201 | T4.rank AS rank2, |
---|
| 202 | T3.class_id AS class_id1, |
---|
| 203 | T4.class_id AS class_id2, |
---|
| 204 | stock, |
---|
| 205 | price01, |
---|
| 206 | price02, |
---|
| 207 | stock_unlimited, |
---|
| 208 | product_code |
---|
| 209 | FROM (dtb_products_class AS T1 |
---|
| 210 | LEFT JOIN dtb_classcategory AS T2 |
---|
| 211 | ON T1.classcategory_id1 = T2.classcategory_id) AS T3 |
---|
| 212 | LEFT JOIN dtb_classcategory AS T4 |
---|
| 213 | ON T3.classcategory_id2 = T4.classcategory_id) AS T5 |
---|
| 214 | LEFT JOIN dtb_products AS T6 |
---|
| 215 | ON product_id_sub = T6.product_id; |
---|
| 216 | |
---|
| 217 | CREATE VIEW vw_category_count AS |
---|
| 218 | SELECT T1.category_id, |
---|
| 219 | T1.category_name, |
---|
| 220 | T1.parent_category_id, |
---|
| 221 | T1.level, |
---|
| 222 | T1.rank, |
---|
| 223 | T2.product_count |
---|
| 224 | FROM dtb_category AS T1 |
---|
| 225 | LEFT JOIN dtb_category_total_count AS T2 |
---|
| 226 | ON T1.category_id = T2.category_id |
---|
| 227 | |
---|