使用UNION ALL将两个表合并合并，然后使用LAST_VALUE解析函数来获取不存在的最新age和dept值：

SELECT id,
       LAST_VALUE(age) IGNORE NULLS
         OVER (PARTITION BY id ORDER BY start_date) AS age,
       LAST_VALUE(dept) IGNORE NULLS
         OVER (PARTITION BY id ORDER BY start_date) AS dept,
       salary,
       start_date
FROM   (
  SELECT id, age, dept, salary, start_date
  FROM   table_a
UNION ALL
  SELECT id, NULL, NULL, salary, start_date
  FROM   table_b
)

其中，对于样本数据：

CREATE TABLE table_a (ID, Age, Dept, Salary, Start_date) AS
SELECT 1, 30, 'A', 1000, DATE '2000-01-01' FROM DUAL UNION ALL
SELECT 1, 31, 'B', 1200, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 2, 25, 'C', 1200, DATE '2021-06-01' FROM DUAL UNION ALL
SELECT 2, 26, 'A', 1300, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 3, 34, 'D', 1400, DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 3, 35, 'C', 1800, DATE '2022-01-01' FROM DUAL;

CREATE TABLE Table_B (ID, Salary, Start_date) AS
SELECT 1, 1500, DATE '2022-06-01' FROM DUAL UNION ALL
SELECT 2, 1800, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 3, 1600, DATE '2021-06-01' FROM DUAL;

输出：
| ID|年龄|部|工资|开始日期|
| --|--|--|--|--|
| 1 | 30 |一| 1000 |2000-01-01 00：00：00|
| 1 | 31 |B| 1200 |2019 -01- 22 00：00：00|
| 1 | 31 |B| 1500 |2019 -06- 21 00：00：00|
| 2 | 25 |C| 1200 |2019 -06-01 00：00：00|
| 2 | 26 |一| 1300 |2019 -01- 22 00：00：00|
| 2 | 26 |一| 1800 |2019 -01- 22 00：00：00|
| 3 | 34 |D| 1400 |2021-01-01 00：00：00|
| 3 | 34 |D| 1600 |2019 -06-01 00：00：00|
| 3 | 35 |C| 1800 |2019 -01- 22 00：00：00|
如果你想INSERT缺失的行，那么在Oracle 12中，你可以使用LATERAL连接和FETCH FIRST ROW ONLY来查找table_b的每行的最新age和dept值：

INSERT INTO table_a (id, age, dept, salary, start_date)
SELECT b.id, a.age, a.dept, b.salary, b.start_date
FROM   table_b b
       LEFT OUTER JOIN LATERAL (
         SELECT a.age, a.dept
         FROM   table_a a
         WHERE  a.id = b.id
         AND    a.start_date <= b.start_date
         ORDER BY start_date DESC
         FETCH FIRST ROW ONLY
       ) a
       ON 1 = 1;

fiddle

如果我得到了正确的-只是没有匹配的ID行，START_DATE组合（在两个表中）应该被添加。这意味着表B中ID = 2的行不会影响表A中的匹配行。如果是这种情况，那么您需要将所有来自表的union与Row_Number（）和Last_Value（）分析函数沿着一些Case表达式组合在一起。

WITH        --  Sample Data
    tbl_a (ID, AGE, DEPT, SALARY, START_DATE) AS
        (   Select 1, 30, 'A', 1000, DATE '2000-01-01' From Dual Union All
            Select 1, 31, 'B', 1200, DATE '2022-01-01' From Dual Union All
            Select 2, 25, 'C', 1200, DATE '2021-06-01' From Dual Union All
            Select 2, 26, 'A', 1300, DATE '2022-01-01' From Dual Union All
            Select 3, 34, 'D', 1400, DATE '2021-01-01' From Dual Union All
            Select 3, 35, 'C', 1800, DATE '2022-01-01' From Dual
        ),
    tbl_b (ID, SALARY, START_DATE) AS
        (   Select 1, 1500, DATE '2022-06-01' From Dual Union All
            Select 2, 1800, DATE '2022-01-01' From Dual Union All
            Select 3, 1600, DATE '2021-06-01' From Dual
        )
--  
--  M a i n    S Q L :
SELECT    ID, 
          Case  When AGE Is Not Null Then AGE
          Else LAST_VALUE(AGE) OVER(Partition By ID Order By ID, START_DATE, TBL Rows Between Unbounded Preceding And 1 Preceding) 
          End "AGE",
          --
          Case  When DEPT Is Not Null Then DEPT 
          Else LAST_VALUE(DEPT) OVER(Partition By ID Order By ID, START_DATE, TBL Rows Between Unbounded Preceding And 1 Preceding)
          End "DEPT",
          --
          SALARY "SALARY", START_DATE "START_DATE"
FROM      ( Select    * 
            From      ( Select    TBL, ID, AGE, DEPT, SALARY, START_DATE, 
                                  Row_Number() OVER(Partition By ID, START_DATE Order By ID, START_DATE, TBL) "RN"
                        From    ( Select        'A' "TBL", ID, AGE, DEPT, SALARY, START_DATE From tbl_a Union All
                                  Select        'B' "TBL", ID, Null, Null, SALARY, START_DATE From tbl_b
                                )
                      ) 
            Where     RN = 1 )
ORDER BY  ID, START_DATE, DEPT

.生成的数据集应为：

--      ID        AGE DEPT     SALARY START_DATE
--  ------ ---------- ---- ---------- ----------
--       1         30 A          1000 01-JAN-00
--       1         31 B          1200 01-JAN-22
--       1         31 B          1500 01-JUN-22   <-- added row
--       2         25 C          1200 01-JUN-21
--       2         26 A          1300 01-JAN-22
--       3         34 D          1400 01-JAN-21
--       3         34 D          1600 01-JUN-21   <-- added row
--       3         35 C          1800 01-JAN-22

.这可以合并到表A中以插入缺失的行：

Merge Into tbl_a a
Using ( ... main sql ... ) x ON(x.ID = a.ID And 
                                x.START_DATE = a.START_DATE) 
When Not Matched
Then  INSERT (a.ID, a.AGE, a.DEPT, a.SALARY, a.START_DATE) 
      Values (x.ID, x.AGE, x.DEPT, x.SALARY, x.START_DATE)

注意：ON子句应该被调整（可能）以确保表A中的行寻址是唯一的。