查了好多文章,都不满足需求,
要求本周为上个周五到昨天,不含今天;
如果今天是周三,是上周五到本周二;
如果今天是周日,是这周五到周六
如果今天正好是周五,是上周五到本周四
最后自己研究了下,验证了一个月的日期,发现是OK的
select
dd.date,
date_format(dd.date,'%w'),
if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))) AS '上周五',
subdate(dd.date,1) as '昨天',
subdate(if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))),7) AS '上上周五',
subdate(if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))),1) as '上周四',
subdate(if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))),14) AS '上上上周五',
subdate(if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))),8) AS '上上周四',
subdate(if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))),21) AS '上上上上周五',
subdate(if(date_format(dd.date,'%w')=6,SUBDATE(dd.date,1),subdate(dd.date,(date_format(dd.date,'%w')+2))),15) AS '上上上周四'
from date_dim dd
where dd.date>='2022-03-01'