Index: branches/version-2/html/install/sql/create_view.sql
===================================================================
--- branches/version-2/html/install/sql/create_view.sql (revision 16546)
+++ branches/version-2/html/install/sql/create_view.sql (revision 18432)
@@ -47,145 +47,111 @@
ON T1.product_id = T2.product_id_sub;
+CREATE VIEW vw_products_allclass_detail AS
+ SELECT
+ dtb_products.product_id,
+ dtb_products."name",
+ dtb_products.deliv_fee,
+ dtb_products.sale_limit,
+ dtb_products.rank,
+ dtb_products.status,
+ dtb_products.product_flag,
+ dtb_products.point_rate,
+ dtb_products.sale_unlimited,
+ dtb_products.comment1,
+ dtb_products.comment2,
+ dtb_products.comment3,
+ dtb_products.comment4,
+ dtb_products.comment5,
+ dtb_products.comment6,
+ dtb_products.note,
+ dtb_products.file1,
+ dtb_products.file2,
+ dtb_products.file3,
+ dtb_products.file4,
+ dtb_products.file5,
+ dtb_products.file6,
+ dtb_products.main_list_comment,
+ dtb_products.main_list_image,
+ dtb_products.main_comment,
+ dtb_products.main_image,
+ dtb_products.main_large_image,
+ dtb_products.sub_title1,
+ dtb_products.sub_comment1,
+ dtb_products.sub_image1,
+ dtb_products.sub_large_image1,
+ dtb_products.sub_title2,
+ dtb_products.sub_comment2,
+ dtb_products.sub_image2,
+ dtb_products.sub_large_image2,
+ dtb_products.sub_title3,
+ dtb_products.sub_comment3,
+ dtb_products.sub_image3,
+ dtb_products.sub_large_image3,
+ dtb_products.sub_title4,
+ dtb_products.sub_comment4,
+ dtb_products.sub_image4,
+ dtb_products.sub_large_image4,
+ dtb_products.sub_title5,
+ dtb_products.sub_comment5,
+ dtb_products.sub_image5,
+ dtb_products.sub_large_image5,
+ dtb_products.sub_title6,
+ dtb_products.sub_comment6,
+ dtb_products.sub_image6,
+ dtb_products.sub_large_image6,
+ dtb_products.del_flg,
+ dtb_products.creator_id,
+ dtb_products.create_date,
+ dtb_products.update_date,
+ dtb_products.deliv_date_id,
+ T4.product_code_min,
+ T4.product_code_max,
+ T4.price01_min,
+ T4.price01_max,
+ T4.price02_min,
+ T4.price02_max,
+ T4.stock_min,
+ T4.stock_max,
+ T4.stock_unlimited_min,
+ T4.stock_unlimited_max,
+ T4.class_count
+ FROM
+ dtb_products
+ LEFT JOIN
+ (
+ SELECT
+ product_id,
+ MIN(product_code) AS product_code_min,
+ MAX(product_code) AS product_code_max,
+ MIN(price01) AS price01_min,
+ MAX(price01) AS price01_max,
+ MIN(price02) AS price02_min,
+ MAX(price02) AS price02_max,
+ MIN(stock) AS stock_min,
+ MAX(stock) AS stock_max,
+ MIN(stock_unlimited) AS stock_unlimited_min,
+ MAX(stock_unlimited) AS stock_unlimited_max,
+ COUNT(*) as class_count
+ FROM dtb_products_class
+ GROUP BY product_id
+ ) AS T4
+ ON dtb_products.product_id = T4.product_id
+;
+
CREATE VIEW vw_products_allclass AS
- SELECT T5.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,
- category_rank,
- T5.category_id,
- T5.del_flg,
- T5.status,
- T5.name,
- T5.comment1,
- T5.comment2,
- T5.comment3,
- T5.rank,
- T5.main_list_comment,
- T5.main_image,
- T5.main_list_image,
- T5.product_flag,
- T5.deliv_date_id,
- T5.sale_limit,
- T5.point_rate,
- T5.sale_unlimited,
- T5.create_date,
- T5.deliv_fee
- FROM
- ((SELECT T1.product_id,
- T1.del_flg,
- T1.status,
- T1.name,
- T1.comment1,
- T1.comment2,
- T1.comment3,
- T1.main_list_comment,
- T1.main_image,
- T1.main_list_image,
- T1.product_flag,
- T1.deliv_date_id,
- T1.sale_limit,
- T1.point_rate,
- T1.sale_unlimited,
- T1.create_date,
- T1.deliv_fee,
- T2.category_id,
- T1.rank
- FROM dtb_products AS T1
- LEFT JOIN dtb_product_categories AS T2
- ON T1.product_id = T2.product_id) AS T3
- RIGHT JOIN
- (SELECT product_id AS product_id_sub,
- MIN(product_code) AS product_code_min,
- MAX(product_code) AS product_code_max,
- MIN(price01) AS price01_min,
- MAX(price01) AS price01_max,
- MIN(price02) AS price02_min,
- MAX(price02) AS price02_max,
- MIN(stock) AS stock_min,
- MAX(stock) AS stock_max,
- MIN(stock_unlimited) AS stock_unlimited_min,
- MAX(stock_unlimited) AS stock_unlimited_max
- FROM dtb_products_class
- GROUP BY product_id) AS T4
- ON T3.product_id = T4.product_id_sub) AS T5
- LEFT JOIN
- (SELECT rank AS category_rank,
- category_id AS sub_category_id
- FROM dtb_category) AS T6
- ON T5.category_id = T6.sub_category_id;
-
-CREATE VIEW vw_products_allclass_detail AS
- SELECT product_id,
- price01_min,
- price01_max,
- price02_min,
- price02_max,
- stock_min,
- stock_max,
- stock_unlimited_min,
- stock_unlimited_max,
- del_flg,
- status,
- name,
- comment1,
- comment2,
- comment3,
- deliv_fee,
- main_comment,
- main_image,
- main_large_image,
- sub_title1,
- sub_comment1,
- sub_image1,
- sub_large_image1,
- sub_title2,
- sub_comment2,
- sub_image2,
- sub_large_image2,
- sub_title3,
- sub_comment3,
- sub_image3,
- sub_large_image3,
- sub_title4,
- sub_comment4,
- sub_image4,
- sub_large_image4,
- sub_title5,
- sub_comment5,
- sub_image5,
- sub_large_image5,
- product_flag,
- deliv_date_id,
- sale_limit,
- point_rate,
- sale_unlimited,
- file1,file2,
- category_id
- FROM (dtb_products AS T1
- RIGHT JOIN
- (SELECT
- product_id AS product_id_sub,
- MIN(price01) AS price01_min,
- MAX(price01) AS price01_max,
- MIN(price02) AS price02_min,
- MAX(price02) AS price02_max,
- MIN(stock) AS stock_min,
- MAX(stock) AS stock_max,
- MIN(stock_unlimited) AS stock_unlimited_min,
- MAX(stock_unlimited) AS stock_unlimited_max
- FROM dtb_products_class
- GROUP BY product_id) AS T2
- 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
- ON T3.category_id = T4.sub_category_id;
+ SELECT
+ alldtl.*,
+ dtb_category.rank AS category_rank,
+ T2.category_id,
+ T2.rank AS product_rank
+ FROM
+ vw_products_allclass_detail AS alldtl
+ LEFT JOIN
+ dtb_product_categories AS T2
+ ON alldtl.product_id = T2.product_id
+ LEFT JOIN
+ dtb_category
+ ON T2.category_id = dtb_category.category_id
+;
CREATE VIEW vw_product_class AS
@@ -205,9 +171,9 @@
stock_unlimited,
product_code
- FROM (dtb_products_class AS T1
+ FROM (dtb_products_class AS T1
LEFT JOIN dtb_classcategory AS T2
- ON T1.classcategory_id1 = T2.classcategory_id) AS T3
+ ON T1.classcategory_id1 = T2.classcategory_id) AS T3
LEFT JOIN dtb_classcategory AS T4
- ON T3.classcategory_id2 = T4.classcategory_id) AS T5
+ ON T3.classcategory_id2 = T4.classcategory_id) AS T5
LEFT JOIN dtb_products AS T6
ON product_id_sub = T6.product_id;
@@ -220,5 +186,5 @@
T1.rank,
T2.product_count
- FROM dtb_category AS T1
+ FROM dtb_category AS T1
LEFT JOIN dtb_category_total_count AS T2
ON T1.category_id = T2.category_id
Index: branches/version-2/html/install/sql/insert_data.sql
===================================================================
--- branches/version-2/html/install/sql/insert_data.sql (revision 18177)
+++ branches/version-2/html/install/sql/insert_data.sql (revision 18432)
@@ -227,6 +227,6 @@
insert into dtb_csv(csv_id,col,disp_name,rank,status,create_date,update_date)values(5,'category_name','カテゴリ名',2,1,now(),now());
insert into dtb_csv(csv_id,col,disp_name,rank,status,create_date,update_date)values(5,'parent_category_id',' 親カテゴリID',3,1,now(),now());
-insert into dtb_csv(csv_id,col,disp_name,rank,status,create_date,update_date)values(5,'level','階層',4,1,now(),now());
-insert into dtb_csv(csv_id,col,disp_name,rank,status,create_date,update_date)values(5,'rank','表示ランク',5,1,now(),now());
+insert into dtb_csv(csv_id,col,disp_name,rank,status,create_date,update_date)values(5,'level','階層',4,2,now(),now());
+insert into dtb_csv(csv_id,col,disp_name,rank,status,create_date,update_date)values(5,'rank','表示ランク',5,2,now(),now());
INSERT INTO dtb_templates (template_code, template_name, create_date, update_date) VALUES('default','デフォルト', now(), now());