MYSQL中计算某天属于第几周，并且取出周一到周日的日期做为周

 

date_format为表dim_date 日期字段

--------------------------------------------------------------------------

select

    YEARWEEK(date_format,1) as yearweek,
     min(date_format) as begin_date,
     max(date_format) as end_date
     from dim_date 
     where 
     substr(date_format,1,7)<=substr(CURRENT_DATE,1,7)
     group by YEARWEEK(date_format,1)

 

------------------------------------------------------------------------------------

select 
yearweek,
begin_date,
end_date,
CONCAT(begin_date,'-',end_date) as '日期周'
FROM
(

select

    YEARWEEK(date_format,1) as yearweek,
     min(date_format) as begin_date,
     max(date_format) as end_date
     from dim_date 
     where 
     substr(date_format,1,7)<=substr(CURRENT_DATE,1,7)
     group by YEARWEEK(date_format,1)

)a