Changeset 16536


Ignore:
Timestamp:
2007/10/22 19:35:41 (17 years ago)
Author:
nanasess
Message:

みやすくインデント

File:
1 edited

Legend:

Unmodified
Added
Removed

  • branches/feature-module-update/html/install/sql/create_view.sql

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