sql 字段为空取上一行的值

Question

如下表，样例所示，code与eff均有为空的情况，如何将为空赋值为上一条不为空的值，如需求所示

Answer 1

SQ语句：

SELECT time,(select code from table2 where code<>'' and time<=t.time order by time desc LIMIT 1) as code ,(select eff from table2 where eff<>'' and time<=t.time order by time desc LIMIT 1) as eff FROM table2 t

结果如下：

测试用的数据如下：