sql 字段为空取上一行的值

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

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

结果如下：

测试用的数据如下：

mysql可以查出来，写张临时表再更新回去

SELECT * FROM csdn_test.sql_null;
SELECT 
    time,
    IFNULL(code,
            (SELECT 
                    t2.code
                FROM
                    sql_null t2
                WHERE
                    t2.time < t1.time
                        AND t2.code IS NOT NULL
                ORDER BY t2.time DESC
                LIMIT 1)),
    IFNULL(eff,
            (SELECT 
                    t3.eff
                FROM
                    sql_null t3
                WHERE
                    t3.time < t1.time AND t3.eff IS NOT NULL
                ORDER BY t3.time DESC
                LIMIT 1))
FROM
    sql_null t1
ORDER BY t1.time

sqlserver 版本的脚本

update tb set code=(select top 1 code from tb b where tb.time<time and isnull(code,'')!='' order by time desc ),
eff=(select top 1 eff from tb b where tb.time<time and isnull(eff,'')!='' order by time desc )
 where isnull(code,'')=='' or isnull(eff,'')=='' ;

思路：用存储过程和游标。

下面是sqlserver的.

IF OBJECT_ID('tmppp') IS NOT NULL
    DROP TABLE tmppp

DECLARE @a TABLE(a VARCHAR(2),code VARCHAR(20),    eff INT)
INSERT @a SELECT 'a',1234, 160
UNION ALL SELECT 'b',NULL, NULL
UNION ALL SELECT 'c',NULL, NULL 
UNION ALL SELECT 'd',NULL, NULL
UNION ALL SELECT 'g',2222, 156 
UNION ALL SELECT 'h',NULL, NULL
UNION ALL SELECT 'e',NULL, NULL
UNION ALL SELECT 's',NULL, NULL

SELECT id = IDENTITY(INT, 1, 1),a,code,eff
INTO   tmppp
FROM   @a 

SELECT a, code = CASE WHEN code IS NOT NULL THEN code
                 ELSE (SELECT TOP 1 code
                       FROM   tmppp
                       WHERE  id < t1.id AND code IS NOT NULL
                       ORDER BY id DESC
                        )
                 END,
       eff = CASE WHEN eff IS NOT NULL THEN eff
             ELSE (SELECT TOP 1 eff
                   FROM   tmppp
                   WHERE  id < t1.id AND eff IS NOT NULL
                   ORDER BY id DESC
                  )
             END
FROM   tmppp t1 
--result
a    1234    160
b    1234    160
c    1234    160
d    1234    160
g    2222    156
h    2222    156
e    2222    156
s    2222    156

用存储过程实现 https://www.5axxw.com/questions/content/zj6o9v

可以通过后台程序来配合完成，根据你的排序规则，将所有的数据信息查询出来，然后找到最近上一条不为空的记录，然后记录下下标，然后通过循环结合下标来赋值即可。

1、使用变量也是可以的，MySQL5.7测试通过：

SELECT a.*, @code := ifnull( a.code, @code ) code1, @eff := ifnull( a.eff, @eff ) eff1
  FROM ( SELECT '2021/7/15' AS time, 1234 code, 160 eff UNION ALL SELECT '2021/8/15', NULL, NULL UNION SELECT '2021/9/15', NULL, NULL UNION ALL
         SELECT '2022/2/15', 1432, 180 UNION ALL SELECT '2022/3/15', NULL, NULL UNION ALL SELECT '2022/10/15', 4444, 200 ) a
     , ( SELECT @code := 0, @eff := 0 ) b;

结果（code1、eff1为需求输出结果）：

MYSQL版本，没有严格按照你给的数据填，不过道理很清晰 可以参考：
update test1 t1 set code = (
select code from (select time,(select code from test1 t2 where t2.time<=t1.time and code is not null order by time desc limit 1) code from test1 t1 ) t where t.time = t1.time limit 1
),eff = (
select eff from (select time,(select eff from test1 t2 where t2.time<=t1.time and eff is not null order by time desc limit 1) eff from test1 t1 ) t where t.time = t1.time limit 1
);

数据量不大可以这样

select 
    t.time
    ,(select t2.code from table_name t2 where t2.time < t1.time and code is not null order by t2.time desc limit 1) as code 
    ,(select t2.fee from table_name t2 where t2.time < t1.time and fee is not null order by t2.time desc limit 1) as code 
from table_name t1

博主参考下这个视频看看能否解决问题
https://b23.tv/BVFmMNC

大家给的结果都可以，但是我最终选择一个适合我的作为最佳答案，感谢大家提供的帮助~