请教各位大神一道MySQL问题

也没有思绪 帮楼主顶顶😂

我会尝试用 case when @restaurant：=@restaurant and @vistit…… then @rank:=…
来排序
排完之后的新表，用rank%3余数来和food表对线。
大致思路这样，但是具体@用法我也有些忘记了。不一定对…… 提供思路

有题的链接吗？让我test一下

试着写了下大致思路可以试试，不会用function方法可能比较笨，有些细节可能有typo什么的：
1. order records by restaurant and visit date
with cte1 as
(select *, row_number() over (partition by restaurant_id order by visit) as rn from restaurant)
2. find the gaps >=3 days without visits
with cte2 as
(select t1.*, row_number() over (partition by restaurant order by rn) as dish_order
from cte1 t1
left join cte1 t2
on t1.restaurant_id=t2.restaurant_id and t1.rn=t2.rn+1
where date_diff(t1.visit,t2.visit)>=3)
3. assign food to gap dates
with cte3 as
(select * from cte2
join (select max(food_id) as max_id from food)
on 1=1)
with cte4 as
(select t1.*,t2.visit as next_gap,food.* from cte3 t1
left join cte3 t2
on t1.restaurant_id=t2.restaurant_id and t1.dish_order=t2.dish_order-1
left join food
on mod(t1.dish_order,t1.max_id)=food.food_id)
4. get the results
select t1.*,t2.food from restaurant t1
left join cte4 t2
on t1.restaurant_id=t2.restaurant_id and t1.visit>=t2.visit and t1.visit<t2.next_gap