MYSQL生成日历表，通常在做报表的时候需要用来生成一个临时表，用来左连接等。

时间：2010-12-29 来源：哥,我还要

通常在做报表的时候会经常使用到。

代码 CREATE DEFINER = 'root'@'localhost' PROCEDURE `create_tmp_yearmonth_tb`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  #变量声明
  declare sourcedate date;
  declare targetdate date;
  declare indexdate date;
  declare index_month int;
  declare step_year_month char(20);

  set sourcedate = date('2010-01-01');
  set targetdate = date('2010-05-01');
  set indexdate = sourcedate;

  set index_month = month(indexdate);
    #执行循环
   while indexdate <= targetdate do
          begin
             set index_month = month(indexdate);
             set step_year_month =  concat(cast(year(indexdate) as char) ,'-',cast(index_month as char),'-01');
             insert tmp_ym_tb(ym) values(step_year_month);
             set step_year_month =  concat(cast(year(indexdate) as char) ,'-',cast((index_month+1) as char),'-01');
             set indexdate = date(step_year_month);
        end;
   end while;

END;

相关阅读更多 +