一种方法是有点黑客，使用 substring_index() / group_concat() :

SELECT t1.t1_id, t1.t1.Company, t1.t1_some_field, last_t2.t2_type 
FROM t1 LEFT JOIN t2
     (SELECT MAX(t2_date) AS Last_Date, company, t2_t1_id,
             SUBSTRING_INDEX(GROUP_CONCAT(t2.t2_type ORDER BY t2.t2_date DESC), ',', 1) as last_t2_type
      FROM t2
      GROUP BY company, t2_t1_id
     ) last_t2
     ON t1.company = last_t2.company AND t1_id = last_t2.t2_t1_id;

另一种方法使用相关子查询：

select t1.*,
       (select t2.t2_type
        from t2
        where t1.company = t2.company and t1.t1_id = t2.t2_t1_id
        order by t2.t2_date desc
        limit 1
       ) as t2_type
from t1;

带索引的 t2(company, t2_t1_id, date) ，这可能有最好的性能。

它可以重写如下，但是，它可能会导致与您的情况相同的查询计划

SELECT t1_id, t1.Company  t1_some_field, t2_type 
FROM t1 
LEFT JOIN t2
ON t1.company = t2.company 
AND t1_id = t2.t2_t1_id
WHERE t2.t2_date =
    (
        SELECT MAX(t2_date) 
        FROM t2
        WHERE t1.company = t2.company AND 
              t1.t1_id = t2.t2_t1_id
        GROUP BY company, t2_t1_id
    )

如果您有mysql支持的窗口函数（从8.0.0版开始），那么还有另一种使用 row_number ，但我建议使用您的解决方案。

试试这个：

SELECT t1_id, t1.Company t1_some_field, 
    (SELECT t2_type 
    FROM t2
    WHERE t2.t2_t1_id = t1.id
    AND t1.company = t2.company
    AND NOT EXISTS(
        SELECT 'NEXT'
        FROM t2 t2next
        WHERE t2.t2_t1_id = t2next.t2_t1_id
        AND t2.company = t2next.company
        AND t2.t2_date > t2next.t2_date)
    )
FROM t1