Changeset 18609 for tmp/version-2_5-test/html/install/sql/create_view.sql
- Timestamp:
- 2010/03/11 10:35:11 (14 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
tmp/version-2_5-test/html/install/sql/create_view.sql
r18432 r18609 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 ; 48 60 49 61 CREATE VIEW vw_products_allclass_detail AS … … 53 65 dtb_products.deliv_fee, 54 66 dtb_products.sale_limit, 67 dtb_products.maker_id, 55 68 dtb_products.rank, 56 69 dtb_products.status, 57 70 dtb_products.product_flag, 58 71 dtb_products.point_rate, 59 dtb_products.sale_unlimited,60 72 dtb_products.comment1, 61 73 dtb_products.comment2, … … 171 183 stock_unlimited, 172 184 product_code 173 FROM (dtb_products_class AS T1 185 FROM (dtb_products_class AS T1 174 186 LEFT JOIN dtb_classcategory AS T2 175 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 187 ON T1.classcategory_id1 = T2.classcategory_id) AS T3 176 188 LEFT JOIN dtb_classcategory AS T4 177 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 189 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 178 190 LEFT JOIN dtb_products AS T6 179 191 ON product_id_sub = T6.product_id; … … 186 198 T1.rank, 187 199 T2.product_count 188 FROM dtb_category AS T1 200 FROM dtb_category AS T1 189 201 LEFT JOIN dtb_category_total_count AS T2 190 202 ON T1.category_id = T2.category_id 191 203 ;
Note: See TracChangeset
for help on using the changeset viewer.