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; |
---|
4 | |
---|
5 | CREATE 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; |
---|
70 | |
---|
71 | CREATE 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; |
---|
81 | |
---|
82 | CREATE 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 | |
---|