mysql - LEFT OUTER JOIN 仅最新记录

Question

我有以下两个表:

t1

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | bob  |  42 |
|  2 | dave |  12 |
|  3 | mary |  78 |
|  4 | kim  |  41 |
+----+------+-----+

t2

+----+-------+------------------+
| id | t1_id |       date       |
+----+-------+------------------+
|  1 |     1 | 12/26/2014 14:32 |
|  2 |     1 | 12/26/2014 16:32 |
|  3 |     1 | 12/27/2014 14:32 |
|  4 |     2 | 12/26/2014 15:32 |
|  5 |     4 | 12/26/2014 14:32 |
|  6 |     4 | 12/27/2014 14:32 |
+----+-------+------------------+

我希望获得以下结果(仅包括从 t2 开始的最新 JOINed 日期):

+----+------+-----+------------------+
| id | name | age |       date       |
+----+------+-----+------------------+
|  1 | bob  |  42 | 12/27/2014 14:32 |
|  2 | dave |  12 | 12/26/2014 15:32 |
|  3 | mary |  78 | null             |
|  4 | kim  |  41 | 12/27/2014 14:32 |
+----+------+-----+------------------+

以下让我很接近，但加入了 t2 的所有比赛。我如何只包括最近的？

SELECT t1.id, t1,name, t1.age, t2.date
FROM t1
LEFT OUTER JOIN t2 ON t2.t1_id=t1.id;

Answer 1

只需使用 group by 和 max():

SELECT t1.id, t1.name, t1.age, MAX(t2.date) as date
FROM t1 LEFT OUTER JOIN
     t2
     ON t2.t1_id = t1.id 
GROUP BY  t1.id, t1.name, t1.age;

编辑:

您可能会发现这更方便并且性能更好:

SELECT t1.*,
       (SELECT MAX(t2.date)
        FROM t2
        WHERE t2.t1_id = t1.id
       ) as date
FROM t1;

为了获得最佳性能，您需要在 t2(t1_id, date) 上建立索引。