SQL加入最后日期

其中一种方法是使用 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, 
    (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 

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

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 ,但我建议使用您的解决方案。