select
CONCAT(
date_format(
if(
date_sub('{date}', 1) between date_add(
dt,
- pmod (datediff(date_add(dt, 3), '1900-01-01'), 7)
) and date_add(dt, -1),
trunc('{date}', 'MM'),
date_add(
dt,
- pmod (datediff(date_add(dt, 3), '1900-01-01'), 7)
)
),
'MM.dd'
),
'-',
date_format(date_add(dt, 0), 'MM.dd')
) week,
from
(
select
A.*,
row_number() over (
ORDER BY
dt desc
) num
from
(
select
*
from
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
where
(
(
dt between date_sub('{date}', 34)
AND '{date}'
)
AND pmod(datediff(date_add(dt, 1), '1900-01-01'), 7) = 4
)
or dt = date_sub('{date}', 1)
) A
) B
where
num < 6
order by
week
结果: