一种方法是从表t1而不是相关子查询创建cte。比如：

WITH T3 as (
  select t.date date, min(x.date) next_workday
  from T1 t join T1 x
  on t.date < x.date
  where x.day_type = 'Weekday'
  group by t.date
)
select T2.process, T2.date run_date, T2.status,
  case when T2.status = 'finished' then T3.next_workday
  else T3.date
  end next_run_date
from T2 join T3
on T2.date = T3.date
order by T2.process, T2.date;
+---------+------------+-----------+---------------+
| process | run_date   | status    | next_run_date |
+---------+------------+-----------+---------------+
| A       | 2020-04-01 | finished  | 2020-04-02    |
| A       | 2020-04-02 | finished  | 2020-04-03    |
| A       | 2020-04-03 | run again | 2020-04-03    |
+---------+------------+-----------+---------------+

然后可以从结果中选择max而不是排序。

考虑到性能，可能有多种解决方案，甚至有更好的解决方案，但这是我的方法。希望有帮助。

select case when status='run_again' then t2_date else t1_date end as needed_date from t2 cross join (select t1_date from t1 where t1.day_type='Weekday' and t1_date>(select max(t2_date) from t2) order by t1.t1_date limit 1)a where t2_date=(select max(t2_date) from t2);