首页 > 代码库 > w-BIG TABLE-view+where-small table
w-BIG TABLE-view+where-small table
w-BIG TABLE
1 DROP PROCEDURE IF EXISTS truncate_insert_mine_category_trend_asinrank_month; 2 DELIMITER /w/ 3 CREATE PROCEDURE truncate_insert_mine_category_trend_asinrank_month () 4 BEGIN 5 TRUNCATE mine_category_trend_asinrank_month ; 6 INSERT INTO mine_category_trend_asinrank_month ( 7 fk_countrycode, 8 fk_categoryid, 9 history_year, 10 history_month 11 ) SELECT 12 CountryCode, 13 CategoryId, 14 YearValue, 15 MonthValue 16 FROM 17 mt_month 18 GROUP BY 19 CountryCode, 20 CategoryId, 21 YearValue, 22 MonthValue ; 23 END/w/ 24 DELIMITER; 25 CALL truncate_insert_mine_category_trend_asinrank_month; 26 27 DROP PROCEDURE IF EXISTS insert_update_mine_category_trend_asinrank_month; 28 DELIMITER /w/ 29 CREATE PROCEDURE insert_update_mine_category_trend_asinrank_month () 30 BEGIN 31 SET @wtab = ‘ mine_category_trend_asinrank_month ‘; 32 SET @wtab_src = ‘mt_month‘; 33 SET @w = 10; 34 SET @wyear_max=(SELECT MAX(YearValue) FROM mt_month)+1; 35 SET @wyear = (SELECT MIN(YearValue) FROM mt_month); 36 37 WHILE @w < 101 DO 38 WHILE @wyear < @wyear_max DO 39 SET @wmonth=(SELECT MIN(MonthValue) FROM mt_month WHERE YearValue=@wyear); 40 SET @wmonth_max=(SELECT MAX(MonthValue) FROM mt_month WHERE YearValue=@wyear)+1; 41 WHILE @wmonth < @wmonth_max DO 42 43 DROP VIEW IF EXISTS wview; 44 SET @wview_where = CONCAT(‘ WHERE YearValue=http://www.mamicode.com/‘,@wyear,‘ AND MonthValue=http://www.mamicode.com/‘,@wmonth,‘ AND TopX=‘,@w); 45 SET @wview = CONCAT(‘CREATE VIEW wview AS SELECT * FROM ‘,@wtab_src,@wview_where,‘;‘); 46 PREPARE stmt0 FROM @wview ; 47 EXECUTE stmt0 ; 48 DROP PREPARE stmt0; 49 50 DROP VIEW IF EXISTS wview1; 51 SET @wview1_where = CONCAT(‘ WHERE history_year=‘,@wyear,‘ AND history_month=‘,@wmonth,‘;‘); 52 SET @wview1 = CONCAT(‘CREATE VIEW wview1 AS SELECT * FROM ‘,@wtab,@wview1_where,‘;‘); 53 PREPARE stmt2 FROM @wview1 ; 54 EXECUTE stmt2 ; 55 DROP PREPARE stmt2; 56 57 SET @wfield = CONCAT(‘unit‘,@w); 58 SET @wnewvalue = CONCAT(‘(SELECT SumUnit FROM wview da WHERE wview1.fk_countrycode = da.CountryCode AND wview1.fk_categoryid = da.CategoryId AND wview1.history_year = da.YearValue AND wview1.history_month = da.MonthValue AND da.TopX=‘,@w,‘ )‘); 59 SET @wfieldb = CONCAT(‘gms‘,@w); 60 SET @wnewvalueb = CONCAT(‘(SELECT SumGms FROM wview da WHERE wview1.fk_countrycode = da.CountryCode AND wview1.fk_categoryid = da.CategoryId AND wview1.history_year = da.YearValue AND wview1.history_month = da.MonthValue AND da.TopX=‘,@w,‘ )‘); 61 62 SET @wpre = CONCAT(‘UPDATE ‘,‘ wview1 ‘,‘ SET ‘,@wfield,‘=‘,@wnewvalue,‘,‘,@wfieldb,‘=‘,@wnewvalueb); 63 PREPARE stmt1 FROM @wpre ; 64 EXECUTE stmt1 ; 65 DROP PREPARE stmt1; 66 67 SET @wmonth=@wmonth+1; 68 END WHILE ; 69 SET @wyear=@wyear+1; 70 END WHILE ; 71 SET @wyear = (SELECT MIN(YearValue) FROM mt_month); 72 SET @w=@w+10; 73 END WHILE ; 74 END/w/ 75 DELIMITER ; 76 CALL insert_update_mine_category_trend_asinrank_month;
w-BIG TABLE-view+where-small table
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。