mysql - 通过LEAD/LAG或递归CTE更新？

有一个表格保存日期和帐户余额。
但是，某些日期无法使用余额。
假设当日期不可用时余额不会更改。
如何更新所有日期的余额信息？

这是一个例子：
表D包含所有有效日期。

2000-01-01
2000-01-02
2000-01-03
2000-01-04
2000-01-05
2000-01-06
2000-01-07
2000-01-08
2000-01-09

表A包含日期和帐户余额。

2000-01-02  $100
2000-01-05  $200
2000-01-09  $700

最终，我想生成一个像这样的表：

2000-01-01  null
2000-01-02  $100
2000-01-03  $100
2000-01-04  $100
2000-01-05  $200
2000-01-06  $200
2000-01-07  $200
2000-01-08  $200
2000-01-09  $700

我考虑过以下几点：


铅和滞后
递归CTE


但是，似乎它们都不适合这种情况。

最佳答案

实现此目的的一种方法是使用第一个值并创建一些排名函数。我正在使用SQL Server

with cte as
(
select '2000-01-01' as Datenew union all
select '2000-01-02' as Datenew union all
select '2000-01-03' as Datenew union all
select '2000-01-04' as Datenew union all
select '2000-01-05' as Datenew union all
select '2000-01-06' as Datenew union all
select '2000-01-07' as Datenew union all
select '2000-01-08' as Datenew union all
select '2000-01-09' as Datenew ), cte2 as (
select '2000-01-02' as DateSal, '100' as Salary union all
select '2000-01-05' as DateSal, '200' as Salary union all
select '2000-01-09' as DateSal, '700' as Salary )


select datenew, Salary = FIRST_VALUE(salary) over (partition by ranking order by datenew) from (
select datenew, salary ,
  sum(case  when DateSal  is not null then 1 end) over (order by datenew) ranking
    from cte c
left join cte2 c2  on  c.Datenew = c2.DateSal ) tst

order by  datenew

--Sum创建运行总计以创建分组，并且第一个值可确保为给定的组获得相同的值。

这是输出

关于mysql - 通过LEAD/LAG或递归CTE更新？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58109333/