在mysql版本8之前，这是相当复杂的。但你可以做到：

select dte, next_dte, 
       datediff(coalesce(next_dte, curdate()), dte) as diff
from (select dte,
             (select min(d2.dte)
              from ((select hiredate as dte
                     from t
                    ) union   -- intentionally remove duplicates
                    (select terminationdate as dte
                     from t
                     where teminationdate is not null
                    )
                   ) d2
              where d2.dte > d.dte
             ) next_dte
      from ((select hiredate as dte
             from t
            ) union   -- intentionally remove duplicates
            (select terminationdate as dte
             from t
             where teminationdate is not null
            )
           ) d
     ) d
order by diff desc
limit 1;

请注意，这将根据当前日期查找最近的期间。您可以通过更换 curdate() 不管你想什么截止日期。如果您不想要最新的时段，请添加 where next_dte is not null 到外部查询。