MySQL 获取指定月中的所有周的详细数据、时间范围（用于周报）

话不多说，直接上代码和效果

 

select date_sub(min(t.d),INTERVAL WEEKDAY(min(t.d)) DAY) start_,
date_sub(max(t.d),INTERVAL WEEKDAY(max(t.d)) - 6 DAY) end_, 
year(t.d) year_, 
week(t.d, 5) week_
from (
    select adddate('2020-01-01', t1.x * 100 + t2.x * 10 + t3.x) d
    from
    (
        select 0 as x 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        
    ) t1,
    (
        select 0 as x 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 as x 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
) t
where DATE_FORMAT(t.d,'%Y-%m') = '2020-10'
group by year(t.d), week(t.d, 5)
order by year_, week_