W-big Table-view+where-small TABLE

W-big TABLE

1 DROP PROCEDURE IF EXISTSTruncate_insert_mine_category_trend_asinrank_month;2DELIMITER/W/3 CREATE PROCEDURETruncate_insert_mine_category_trend_asinrank_month ()4 BEGIN5 TRUNCATEMine_category_trend_asinrank_month;6 INSERT  intoMine_category_trend_asinrank_month (7 Fk_countrycode,8 Fk_categoryid,9 History_year,Ten History_month One)SELECT A CountryCode, - CategoryId, - Yearvalue, the Monthvalue -  from - Mt_month - GROUP  by + CountryCode, - CategoryId, + Yearvalue, A Monthvalue; at END/W/ - DELIMITER; - Call Truncate_insert_mine_category_trend_asinrank_month; -  - DROP PROCEDURE IF EXISTSInsert_update_mine_category_trend_asinrank_month; -DELIMITER/W/ in CREATE PROCEDUREInsert_update_mine_category_trend_asinrank_month () - BEGIN to SET @wtab = 'Mine_category_trend_asinrank_month'; + SET @wtab_src = 'Mt_month'; - SET @w = Ten; the SET @wyear_max=(SELECT MAX(Yearvalue) fromMt_month)+1; * SET @wyear =(SELECT MIN(Yearvalue) frommt_month); $ Panax Notoginseng  while @w < 101 Do -      while @wyear < @wyear_max Do the         SET @wmonth=(SELECT MIN(Monthvalue) fromMt_monthWHEREYearvalue=@wyear); +         SET @wmonth_max=(SELECT MAX(Monthvalue) fromMt_monthWHEREYearvalue=@wyear)+1; A          while @wmonth < @wmonth_max Do the  +                 DROP VIEW IF EXISTSWview; -                 SET @wview_where =CONCAT ('WHERE yearvalue=',@wyear,'and monthvalue=',@wmonth,'and topx=',@w); $                 SET @wview =CONCAT ('CREATE VIEW Wview as SELECT * from',@wtab_src,@wview_where,';'); $                 PREPAREStmt0 from @wview ; -                 EXECUTEstmt0; -                 DROP PREPAREstmt0; the  -                 DROP VIEW IF EXISTSWview1;Wuyi                 SET @wview1_where =CONCAT ('WHERE history_year=',@wyear,'and history_month=',@wmonth,';'); the                 SET @wview1 =CONCAT ('CREATE VIEW wview1 as SELECT * from',@wtab,@wview1_where,';'); -                 PREPAREStmt2 from @wview1 ; Wu                 EXECUTEstmt2; -                 DROP PREPAREstmt2; About  $                 SET @wfield =CONCAT ('Unit',@w); -                 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,'  )'); -                 SET @wfieldb =CONCAT ('GMS',@w); -                 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,'  )'); A                                  +                 SET @wpre =CONCAT ('UPDATE','Wview1','SET',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb); the                 PREPAREStmt1 from @wpre ; -                 EXECUTESTMT1; $                 DROP PREPARESTMT1; the  the         SET @wmonth=@wmonth+1; the         END  while ; the     SET @wyear=@wyear+1; -     END  while ; in SET @wyear =(SELECT MIN(Yearvalue) frommt_month); the SET @w=@w+Ten; the END  while ; About END/W/ the DELIMITER; theCall Insert_update_mine_category_trend_asinrank_month;

W-big Table-view+where-small TABLE