谢谢大家，我想我自己解决了这个问题，使用 WITH 并选择 MAX(LAST_UPDATED_DATE) OVER (PARTITION BY PROJECTID,TASKID) AS LATEST_UPDATE

然后从仅由 WITH 定义的临时表中选择 LATEST_UPDATE = LAST_UPDATED_DATE 的行

WITH temptable 
AS (SELECT TA.PROJECTID,TA.TASKID,TA.DUEDATE,MH.DATE_EXECUTION,TA.LAST_UPDATED_DATE,MAX (TA.LAST_UPDATED_DATE) OVER (PARTITION BY TA.PROJECTID,TA.TASKID) AS LATEST_UPDATED
  FROM (TASKS TA
        INNER JOIN
        (SELECT PA.PROJECTID,MIN(PA.DATE_EXECUTION)
           FROM APPROVALS pa
         GROUP BY pa.PROJECTID) MH
           ON TA.PROJECTID = MH.PROJECTID)
 WHERE     
        TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION)

SELECT PROJECTID,TASKID,DUEDATE,DATE_EXECUTION,LAST_UPDATED_DATE
FROM temptable
WHERE LAST_UPDATED_DATE = LATEST_UPDATED

由于所有记录的due_date都不同，因此您可以从选择列表中删除due_date列，这将解决您的问题，或者您可以尝试以下方法-

SELECT TA1.PROJECTID,TA1.TASKID,TA2.DATE_EXECUTION,TA1.DUEDATE,TA1.LAST_UPDATED_DATE
  FROM TASKS TA1
 INNER JOIN (SELECT TA.PROJECTID,MAX(TA.LAST_UPDATED_DATE) AS LAST_UPDATED_DATE
               FROM TASKS TA
              INNER JOIN (SELECT PA.PROJECTID,MIN(PA.DATE_EXECUTION)
                            FROM APPROVALS pa
                           GROUP BY pa.PROJECTID) MH ON TA.PROJECTID = MH.PROJECTID)
              WHERE TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION
              GROUP BY TA.PROJECTID,MH.DATE_EXECUTION) TA2 ON TA1.PROJECTID = TA2.PROJECTID
                                             AND TA1.TASKID = TA2.TASKID
                                             AND TA1.DUEDATE = TA2.DUEDATE
                                             AND TA1.LAST_UPDATED_DATE = TA2.LAST_UPDATED_DATE;

我会像这样使用 correlated subquery：

select  ta.projectid,ta.taskid,ta.duedate,mh.date_execution,ta.last_updated_date
from    tasks                                                 ta
   join ( select    projectid,min(date_execution) as date_execution
          from      approvals
          group by  projectid
        )                                                     mh
     on ta.projectid = mh.projectid
where   ta.last_updated_date =   (  select    max(x.last_updated_date) as last_updated_date
                                    from      tasks   x 
                                    where     x.projectid = ta.projectid 
                                          and x.last_updated_date <= mh.date_execution      );

从 DUEDATE 中删除 GROUP BY，然后使用 KEEP (DENSE_RANK LAST LAST_UPDATED_DATE) 对其进行聚合，首先过滤掉聚合中不是最新的 LAST_UPDATED_DATE 的所有行

SELECT TA.PROJECTID,MAX(TA.DUEDATE) KEEP (DENSE_RANK LAST ORDER BY TA.LAST_UPDATED_DATE)
         AS DUEDATE,MAX(TA.LAST_UPDATED_DATE) AS LAST_UPDATED_DATE
FROM   TASKS TA
       INNER JOIN (
         SELECT PA.PROJECTID,MIN(PA.DATE_EXECUTION)
         FROM   APPROVALS pa
         GROUP BY pa.PROJECTID
       ) MH
       ON TA.PROJECTID = MH.PROJECTID)
WHERE  TA.LAST_UPDATED_DATE <= MH.DATE_EXECUTION
GROUP BY
       TA.PROJECTID,MH.DATE_EXECUTION