一个选项是在子查询中定义每个ID的最大日期。这将导致每个ID 1行，因此您可以在ID列上连接它：

SELECT      a.ID, b.A_NAME, c.A_DATE 
FROM        tbl_a a 
LEFT JOIN   tbl_b b ON (b.ID = a.ID)
LEFT JOIN   (Select ID, Max(A_DATE) "A_DATE" From tbl_c Group By ID) c ON(c.ID = a.ID)

字符串
.用一些虚拟样本数据进行测试

WITH
    tbl_a AS
        (   Select LEVEL "ID" From Dual Connect By LEVEL <= 3   ),
--          ID
--  ----------
--           1
--           2
--           3

    tbl_b AS 
        (   Select LEVEL "ID", 'Name ' || LEVEL "A_NAME" From Dual Connect By LEVEL <= 3 ),
--          ID A_NAME                                       
--  ---------- ---------------------------------------------
--           1 Name 1                                       
--           2 Name 2                                       
--           3 Name 3            
    tbl_c AS
        (   Select LEVEL "ID", To_Date('23.10.2023', 'dd.mm.yyyy') - LEVEL + 3 "A_DATE" From Dual Connect By LEVEL <= 2 Union All
            Select LEVEL "ID", To_Date('23.10.2023', 'dd.mm.yyyy') - LEVEL + 1 "A_DATE" From Dual Connect By LEVEL <= 3 
        )
--          ID A_DATE   
--  ---------- ---------
--           1 25-OCT-23
--           2 24-OCT-23
--           1 23-OCT-23
--           2 22-OCT-23
--           3 21-OCT-23

型
结果作为

--          ID A_NAME                  A_DATE     
--  ---------- ---------------------- -----------
--           1 Name 1                 25-OCT-23
--           2 Name 2                 24-OCT-23
--           3 Name 3                 21-OCT-23

型

一个非常干净的解决方案是使用 * 横向内联视图 *：

SELECT a.id
      ,b.a_name
      ,c.a_date
  FROM tbl_a a
  LEFT JOIN tbl_b b ON ( b.id = a.id )
 CROSS JOIN LATERAL ( SELECT c.a_date
                        FROM tbl_c c
                       WHERE c.id = a.id
                       ORDER BY a_date DESC
                       FETCH FIRST 1 ROWS ONLY
                    ) c

字符串

使用OUTER LATERAL联接：

SELECT *
FROM   TABLE_A a 
       LEFT OUTER JOIN TABLE_B b
       ON b.ID = a.ID
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   TABLE_C c
         WHERE  c.id = a.id
         ORDER BY c.datecolumn DESC
         FETCH FIRST ROW ONLY        -- or FETCH FIRST ROW WITH TIES
      )
      ON (1 = 1)                     -- The join condition is in the `WHERE` clause but
                                     -- an `ON` clause is required for an OUTER JOIN.

字符串
或者使用ROW_NUMBER解析函数（或者如果你想要与FETCH FIRST ROW WITH TIES相同的行为，使用RANK解析函数）：

SELECT *
FROM   TABLE_A a 
       LEFT OUTER JOIN TABLE_B b
       ON b.ID = a.ID
       LEFT OUTER JOIN LATERAL (
         SELECT c.*,
                ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.datecolumn DESC) AS rnk
         FROM   TABLE_C c
      ) c
      ON (c.id = a.id AND c.rnk = 1)

型
fiddle