对于 MySQL 8:
with recursive rcte(dt_id, col, value) as (
(
select dt_id, col, value
from mytable
order by dt_id
limit 1
)
union all
select r.dt_id + interval 1 day
, coalesce(t.col, r.col)
, coalesce(t.value, r.value)
from rcte r
left join mytable t on t.dt_id = r.dt_id + interval 1 day
where r.dt_id < (select max(dt_id) from mytable)
)
select r.col, r.dt_id, r.value
from rcte r
order by r.dt_id
数据库小提琴 https://www.db-fiddle.com/f/sYjyWPoc44MstYvPYcD1Bs/0
递归查询将逐行构建,从第一个日期到最后一个日期递增日期。这value
(and col
) 取自原始表,该表按日期左连接。如果原始表没有日期行,则采用递归中最后一行的值。
对于旧版本,您可以使用日历表和左连接 ON 子句中的子查询来获取最后的现有值:
select b.col, c.date_id, b.value
from time_table c
left join balance b on b.dt_id = (
select max(dt_id)
from balance b1
where b1.dt_id <= c.date_id
)
where c.date_id >= (select min(dt_id) from balance)
and c.date_id <= (select max(dt_id) from balance)
数据库小提琴 https://www.db-fiddle.com/f/4fJSJzJrFySt4eLw5TUsaT/0
Update
由于问题已经改变:
select b.col, c.date_id, b.value
from (
select col, min(dt_id) as min_dt, max(dt_id) as max_dt
from balance
group by col
) i
join time_table c
on c.date_id >= i.min_dt
and c.date_id <= i.max_dt
left join balance b
on b.col = i.col
and b.dt_id = (
select max(dt_id)
from balance b1
where b1.dt_id <= c.date_id
and b1.col = i.col
)
order by b.col, c.date_id
数据库小提琴 https://www.db-fiddle.com/f/9CBq6BFRRXi8AyntxpDzQv/0
确保您有索引(col, dt_id)
。在最好的情况下,它将是主键。date_id
in the time_table
还应该是索引或主键。