Changeset 18432 for branches/version-2/html/install/sql/create_view.sql
- Timestamp:
- 2009/12/07 15:08:06 (14 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/version-2/html/install/sql/create_view.sql
r16546 r18432 47 47 ON T1.product_id = T2.product_id_sub; 48 48 49 CREATE VIEW vw_products_allclass_detail AS 50 SELECT 51 dtb_products.product_id, 52 dtb_products."name", 53 dtb_products.deliv_fee, 54 dtb_products.sale_limit, 55 dtb_products.rank, 56 dtb_products.status, 57 dtb_products.product_flag, 58 dtb_products.point_rate, 59 dtb_products.sale_unlimited, 60 dtb_products.comment1, 61 dtb_products.comment2, 62 dtb_products.comment3, 63 dtb_products.comment4, 64 dtb_products.comment5, 65 dtb_products.comment6, 66 dtb_products.note, 67 dtb_products.file1, 68 dtb_products.file2, 69 dtb_products.file3, 70 dtb_products.file4, 71 dtb_products.file5, 72 dtb_products.file6, 73 dtb_products.main_list_comment, 74 dtb_products.main_list_image, 75 dtb_products.main_comment, 76 dtb_products.main_image, 77 dtb_products.main_large_image, 78 dtb_products.sub_title1, 79 dtb_products.sub_comment1, 80 dtb_products.sub_image1, 81 dtb_products.sub_large_image1, 82 dtb_products.sub_title2, 83 dtb_products.sub_comment2, 84 dtb_products.sub_image2, 85 dtb_products.sub_large_image2, 86 dtb_products.sub_title3, 87 dtb_products.sub_comment3, 88 dtb_products.sub_image3, 89 dtb_products.sub_large_image3, 90 dtb_products.sub_title4, 91 dtb_products.sub_comment4, 92 dtb_products.sub_image4, 93 dtb_products.sub_large_image4, 94 dtb_products.sub_title5, 95 dtb_products.sub_comment5, 96 dtb_products.sub_image5, 97 dtb_products.sub_large_image5, 98 dtb_products.sub_title6, 99 dtb_products.sub_comment6, 100 dtb_products.sub_image6, 101 dtb_products.sub_large_image6, 102 dtb_products.del_flg, 103 dtb_products.creator_id, 104 dtb_products.create_date, 105 dtb_products.update_date, 106 dtb_products.deliv_date_id, 107 T4.product_code_min, 108 T4.product_code_max, 109 T4.price01_min, 110 T4.price01_max, 111 T4.price02_min, 112 T4.price02_max, 113 T4.stock_min, 114 T4.stock_max, 115 T4.stock_unlimited_min, 116 T4.stock_unlimited_max, 117 T4.class_count 118 FROM 119 dtb_products 120 LEFT JOIN 121 ( 122 SELECT 123 product_id, 124 MIN(product_code) AS product_code_min, 125 MAX(product_code) AS product_code_max, 126 MIN(price01) AS price01_min, 127 MAX(price01) AS price01_max, 128 MIN(price02) AS price02_min, 129 MAX(price02) AS price02_max, 130 MIN(stock) AS stock_min, 131 MAX(stock) AS stock_max, 132 MIN(stock_unlimited) AS stock_unlimited_min, 133 MAX(stock_unlimited) AS stock_unlimited_max, 134 COUNT(*) as class_count 135 FROM dtb_products_class 136 GROUP BY product_id 137 ) AS T4 138 ON dtb_products.product_id = T4.product_id 139 ; 140 49 141 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 FROM 81 ((SELECT T1.product_id, 82 T1.del_flg, 83 T1.status, 84 T1.name, 85 T1.comment1, 86 T1.comment2, 87 T1.comment3, 88 T1.main_list_comment, 89 T1.main_image, 90 T1.main_list_image, 91 T1.product_flag, 92 T1.deliv_date_id, 93 T1.sale_limit, 94 T1.point_rate, 95 T1.sale_unlimited, 96 T1.create_date, 97 T1.deliv_fee, 98 T2.category_id, 99 T1.rank 100 FROM dtb_products AS T1 101 LEFT JOIN dtb_product_categories AS T2 102 ON T1.product_id = T2.product_id) AS T3 103 RIGHT JOIN 104 (SELECT product_id AS product_id_sub, 105 MIN(product_code) AS product_code_min, 106 MAX(product_code) AS product_code_max, 107 MIN(price01) AS price01_min, 108 MAX(price01) AS price01_max, 109 MIN(price02) AS price02_min, 110 MAX(price02) AS price02_max, 111 MIN(stock) AS stock_min, 112 MAX(stock) AS stock_max, 113 MIN(stock_unlimited) AS stock_unlimited_min, 114 MAX(stock_unlimited) AS stock_unlimited_max 115 FROM dtb_products_class 116 GROUP BY product_id) AS T4 117 ON T3.product_id = T4.product_id_sub) AS T5 118 LEFT JOIN 119 (SELECT rank AS category_rank, 120 category_id AS sub_category_id 121 FROM dtb_category) AS T6 122 ON T5.category_id = T6.sub_category_id; 123 124 CREATE VIEW vw_products_allclass_detail AS 125 SELECT product_id, 126 price01_min, 127 price01_max, 128 price02_min, 129 price02_max, 130 stock_min, 131 stock_max, 132 stock_unlimited_min, 133 stock_unlimited_max, 134 del_flg, 135 status, 136 name, 137 comment1, 138 comment2, 139 comment3, 140 deliv_fee, 141 main_comment, 142 main_image, 143 main_large_image, 144 sub_title1, 145 sub_comment1, 146 sub_image1, 147 sub_large_image1, 148 sub_title2, 149 sub_comment2, 150 sub_image2, 151 sub_large_image2, 152 sub_title3, 153 sub_comment3, 154 sub_image3, 155 sub_large_image3, 156 sub_title4, 157 sub_comment4, 158 sub_image4, 159 sub_large_image4, 160 sub_title5, 161 sub_comment5, 162 sub_image5, 163 sub_large_image5, 164 product_flag, 165 deliv_date_id, 166 sale_limit, 167 point_rate, 168 sale_unlimited, 169 file1,file2, 170 category_id 171 FROM (dtb_products AS T1 172 RIGHT JOIN 173 (SELECT 174 product_id AS product_id_sub, 175 MIN(price01) AS price01_min, 176 MAX(price01) AS price01_max, 177 MIN(price02) AS price02_min, 178 MAX(price02) AS price02_max, 179 MIN(stock) AS stock_min, 180 MAX(stock) AS stock_max, 181 MIN(stock_unlimited) AS stock_unlimited_min, 182 MAX(stock_unlimited) AS stock_unlimited_max 183 FROM dtb_products_class 184 GROUP BY product_id) AS T2 185 ON T1.product_id = T2.product_id_sub) AS T3 186 LEFT JOIN (SELECT rank AS category_rank, 187 category_id AS sub_category_id 188 FROM dtb_category) AS T4 189 ON T3.category_id = T4.sub_category_id; 142 SELECT 143 alldtl.*, 144 dtb_category.rank AS category_rank, 145 T2.category_id, 146 T2.rank AS product_rank 147 FROM 148 vw_products_allclass_detail AS alldtl 149 LEFT JOIN 150 dtb_product_categories AS T2 151 ON alldtl.product_id = T2.product_id 152 LEFT JOIN 153 dtb_category 154 ON T2.category_id = dtb_category.category_id 155 ; 190 156 191 157 CREATE VIEW vw_product_class AS … … 205 171 stock_unlimited, 206 172 product_code 207 FROM (dtb_products_class AS T1 173 FROM (dtb_products_class AS T1 208 174 LEFT JOIN dtb_classcategory AS T2 209 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 175 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 210 176 LEFT JOIN dtb_classcategory AS T4 211 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 177 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 212 178 LEFT JOIN dtb_products AS T6 213 179 ON product_id_sub = T6.product_id; … … 220 186 T1.rank, 221 187 T2.product_count 222 FROM dtb_category AS T1 188 FROM dtb_category AS T1 223 189 LEFT JOIN dtb_category_total_count AS T2 224 190 ON T1.category_id = T2.category_id
Note: See TracChangeset
for help on using the changeset viewer.