假设这是您的表：

SQL> select * from employee order by empno, year desc;

     EMPNO JOB             YEAR NEXT_JOB
---------- --------- ---------- --------------------
       111 paramedic       2022
       111 doctor          2021
       111 student         2020
       222 waiter          2022
       222 student         2021
       333 nurse           2022

6 rows selected.

使用LEAD分析函数更新next_job：

SQL> merge into employee a
  2    using (select empno,
  3                  year,
  4                  lead(job) over (partition by empno order by year) next_job
  5           from employee
  6          ) b
  7    on (a.empno = b.empno and a.year = b.year)
  8    when matched then update set
  9      a.next_job = b.next_job;

6 rows merged.

结果：

SQL> select * from employee order by empno, year desc;

     EMPNO JOB             YEAR NEXT_JOB
---------- --------- ---------- --------------------
       111 paramedic       2022
       111 doctor          2021 paramedic
       111 student         2020 doctor
       222 waiter          2022
       222 student         2021 waiter
       333 nurse           2022

6 rows selected.

SQL>

您可以使用窗口函数LAG进行此操作

SELECT
"employee number", "job", "year",
COALESCE(LAG("job") OVER(PARTITION BY "employee number" ORDER BY "year" DESC),' ') As Last_Job
FROM table1

| 员工编号|工作|年份|最后作业|
| - -|- -|- -|- -|
| 一一一|护理人员|小行星2022| |
| 一一一|医生|小行星2021|护理人员|
| 一一一|学生|小行星2020|医生|
| 二百二十二|侍者|小行星2022| |
| 二百二十二|学生|小行星2021|侍者|
| 三百三十三|护理人员|小行星2022| |
fiddle