1. 生成指定开始和结束时间内所有月份，并根据此表生成跨月查询场景的所有排列组合： ``sql CREATE TABLEdim_ym(d_yearvarchar(10) NOT NULL,d_monthvarchar(14) NOT NULL, PRIMARY KEY (d_year,d_month`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    insert into dim_ym
    select DATE_FORMAT(date_add(‘1970-01-01 00:00:00’, interval row MONTH),’%Y’) as d_year,DATE_FORMAT(date_add(‘1970-01-01 00:00:00’, interval row MONTH),’%Y%m’) d_month from ( SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 2 union all select 3 union all select 4 ) t4, (SELECT @row:=-1) r ) se where DATE_FORMAT(date_add(‘1970-01-01 00:00:00’, interval row MONTH),’%Y%m’) <= DATE_FORMAT(‘2100-12-31 00:00:00’,’%Y%m’) ;

    SELECT a.d_month as start_ds ,b.d_month as end_ds from dim_ym a join dim_ym b on a.d_year = b.d_year where b.d_month >= a.d_month group by a.d_month, b.d_month;

    — 结果 202201 202201 202201 202202 202201 202203 202201 202204 202201 202205 202201 202206 202201 202207 202201 202208 202201 202209 202201 202210 202201 202211 202201 202212 202202 202202 202202 202203 202202 202204 202202 202205 202202 202206 202202 202207 202202 202208 … … ```