mysql - SQL加入最后日期

Question

你好，我试图通过 2 列连接表中的类型，这提供了多个行，所以这不起作用

SELECT t1_id, t1.Company  t1_some_field, t2_type 
FROM t1 
LEFT JOIN t2 ON t1.Company = t2.Company AND t1_id = t2_t1_id

表 t2

t2.Company + t2_t1_id 有多行，t2_date

我需要加入 t2_type 和最后一个 t2_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
LEFT JOIN
    (SELECT MAX(t2_date) AS Last_Date, company, t2_t1_id
        FROM t2
        GROUP BY company, t2_t1_id) last_t2
ON t1.company = last_t2.company 
AND t1_id = last_t2.t2_t1_id

WHERE t2_date = Last_Date;

看起来这可行，但我认为应该是一种更简单的方法。

Answer 1

一个方法有点hack，使用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) 的索引，这可能具有最佳性能。