Changeset 17549 for branches/comu-ver2/html/install/sql/create_view.sql
- Timestamp:
- 2008/08/21 00:02:54 (16 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/comu-ver2/html/install/sql/create_view.sql
r17537 r17549 1 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; 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 ; 12 15 13 16 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; 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 ; 31 37 32 38 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; 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 ; 48 153 49 154 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; 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 ; 192 168 193 169 CREATE VIEW vw_product_class AS … … 225 201 LEFT JOIN dtb_category_total_count AS T2 226 202 ON T1.category_id = T2.category_id 227 203 ;
Note: See TracChangeset
for help on using the changeset viewer.