MySQL gets the month of the specified time period

# When you write a stored procedure, you may encounter the time that you enter by entering the time of year # below by trying to fix the problem # idea: # Create an attempt for years, months  here Create a 2010-2020 year drop view if exists v_year; CREATE VIEW v_year as (select ' as Yearname) union ALL (SELECT ') ') union ALL (SELECT ' 2013 ') UNION ALL (SELECT ') "union All (SELECT ')" union All (SELECT ') "union All (SELECT ') ') union All (SELECT ' 2018 ') UNION ALL (SELECT ' 2019 '), union All (SELECT ' 2020 '); #  creates a 1-12 month month drop view if exists v_month; CREATE VIEW v_month as (select ' MonthName ') union ALL (SELECT ' the ") union All (SELECT '") union All (SELECT ') ' Union A LL (SELECT ') union ALL (SELECT ') "union All (SELECT ') ') union-All (SELECT ') ') Union ' all ' (select ' ') ', ' All '" UNION ALL (SELECT ' 12 '); # Two attempts to create the underlying data as another attempt (containing the year of the year). Drop View if exists V_year_month;crea Te View V_year_month as (SELECT * from V_year,v_month);

　

# view V_year_month contains 2010-2020 and corresponding 1-12 months of data 132 Bar #  Here is a stored procedure written in learning #  where tables are created by month, for example, in the format: testrecored201703 = 20 March 17 Data Sheet #  If the parameter is 2017-05-09 11:28:27  2017-11-09 11:28:39 You need to query #  testrecored201705, testrecored201706, testrecored201707, testrecored201708, testrecored201709 #  so need to get 201705, 201706, 201707, 201708, 201709 through the cursor can traverse

　　

drop procedure if exists Pro40000;delimiter//create procedure pro40000 (StartDateTime datetime,enddatetime datetime) Begin DECLARE Yearmonth varchar;d eclare normhourtable varchar;d eclare normnormtable varchar (;D eclaredone INT DEFAULT false;declare cursor_yearmonth cursor for SELECT concat_ws (", yearname,monthname) as Yearmonth from V_year_mo Nth where yearname between Date_format (StartDateTime, '%Y ') and Date_format (EndDateTime, '%Y ') and monthname between Date_ FORMAT (StartDateTime, '%m ') and Date_format (EndDateTime, '%m ');D eclare CONTINUE HANDLER for don't FOUND SET done = TRUE; TRUNCATE table T_internetsumrecord;open Cursor_yearmonth; Read_loop:loopfetch cursor_yearmonth into Yearmonth; IF done Thenleave Read_loop; END If;set normhourtable = concat (' Internetsumrecord_hour ', yearmonth); set normnormtable = Concat (' Internetsumrecord_ Norm_hour ', yearmonth); set @SQL = CONCAT (' INSERT into T_internetsumrecord ', ' SELECT ctinfo. NAMECN, Loginusername,cast (sum (case is normhour.normid=6 then noRmhour.normtotal else 0 End)/sum (case if Normhour.normid=6 then Normhour.normcount else 0 end) as decimal (10,2)) as Ping Offsettime,cast (sum (case normhour.normid=171 then normhour.normtotal else 0 end)/sum (case when normhour.normid=171  Then Normhour.normcount else 0 end)/-as Decimal (10,2) as Firstscreentime,cast (sum (case-normhour.normid=83 Then Normhour.normtotal else 0 End)/sum (case is normhour.normid=83 then Normhour.normcount else 0 end)/As decimal (10, 2) as Netshowoffsettime from ', normhourtable, ' Sumrecord left JOIN ', normnormtable, ' Normhour on Normhour. Superid = Sumrecord.id ', ' left join Ctinfo on Sumrecord.ctid=ctinfo.ctid where sumrecord.servicetype= ', ' ' ' + ', ' and Su Mrecord.subservicetype= ', ' ' xx ', ' and sumrecord.expservicetype= ', ' 0 ', ' GROUP by Ctinfo '. Namecn,loginusername LIMIT ', 0, ', ', '); # SELECT @SQL; PREPARE Stms from @SQL; EXECUTE stms;deallocate PREPARE Stms; END LOOP; CLOSE Cursor_yearmonth; --Close the cursor end//delimiter;

　　

MySQL gets the month of the specified time period