17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> create table test(id int, name varchar(10), num int);
Table created
SQL> begin
2 insert into test values(1, 'xx', 1);
3 insert into test values(2, 'xx', 11);
4 insert into test values(3, 'xx', null);
5 insert into test values(4, 'xx', null);
6 insert into test values(5, 'xx', 45);
7 insert into test values(6, 'xx', 56);
8 insert into test values(7, 'xx', null);
9 insert into test values(8, 'xx', 59);
10 end;
11 /
PL/SQL procedure successfully completed
SQL> col id format 99999999;
SQL> col num format 99999999;
SQL> col new_num format 99999999;
SQL> select id,
2 name,
3 num,
4 nvl(num,lag(num, 1) ignore nulls over(order by id)) new_num
5 from test t;
ID NAME NUM NEW_NUM
-------- ---------- -------- --------
1 xx 1 1
2 xx 11 11
3 xx 11
4 xx 11
5 xx 45 45
6 xx 56 56
7 xx 56
8 xx 59 59
8 rows selected
SQL> drop table test purge;
Table dropped
SQL>