如果您决定升级到MySQL 8.0,这是一个递归CTE,它将生成planning_start_date
至planning_end_date
之间的所有日期以及所需的每日数量的列表:
WITH RECURSIVE cte AS (
SELECT planning_start_date AS date,planning_end_date,quantity / (DATEDIFF(planning_end_date,planning_start_date) + 1) AS daily_qty
FROM test
UNION ALL
SELECT date + INTERVAL 1 DAY,daily_qty
FROM cte
WHERE date < planning_end_date
)
SELECT `date`,daily_qty
FROM cte
ORDER BY `date`
Demo on dbfiddle
,
在MySLQ 8+中,您可以像这样使用递归CTE:
with recursive cte(dte,quantity,days) as (
select planning_start_date as dte,datediff(planning_end_date,planning_start_date) + 1 as days
from t
union all
select dte + interval 1 day as dte,days
from cte
where dte < planning_end_date
)
select dte,quantity / days
from cte;
Here是db 小提琴。
在早期版本中,您需要某种数字表。例如,如果表中有足够的行,则可以使用它:
select (planning_start_date + interval n.n day),quantity / (datediff(planning_end_date,planning_start_date) + 1)
from t join
(select (@rn := @rn + 1) as n
from t cross join
(select @rn := 0) params
) n
on planning_start_date + interval n.n day <= planning_end_date;
您可以使用任何足以容纳n
的表。
本文链接:https://www.f2er.com/2913734.html