MySQL LEFT JOIN仅取决于MAX()值的1行 [英] MySQL LEFT JOIN only 1 row depending on MAX() value

问题描述

ID | Name
1    Brain
2    Amy

表2:

ID | WorkDay    | MissionCode
1    2019-01-01    2360
1    2019-02-01    2470
2    2019-01-01    4470
2    2019-02-01    7210

我想要实现的是，我想获取表1的所有字段并在表2上保持左联接，但仅是最新的WorkDay值，如下所示:

What I want to achieve is, I want to get all table 1 fields and left join on the table 2 but only the latest WorkDay values, like this:

ID | Name | WorkDay    | MissionCode
1    Brain  2019-02-01   2470
2    Amy    2019-02-01   7210

到目前为止，我尝试过的是:

What I've tried so far is:

SELECT 
    table1.*, t2.WorkDay, t2.MissionCode
FROM 
    table1
LEFT JOIN
    (SELECT
        *
     FROM
        table2
     ORDER BY
        WorkDay DESC
     LIMIT 0,1) AS t2
ON
   t2.id = table1.id

但是它从table2返回NULL值，像这样:

But it returns NULL values from table2, like this:

ID | Name | WorkDay    | MissionCode
1    Brain  NULL         NULL
2    Amy    NULL         NULL

我测试了相同的查询，在内部联接的select命令中添加了额外的WHERE子句，它成功了.

I tested the same query adding extra WHERE clause to the inner join's select command and it succedeed.

SELECT 
    table1.*, t2.WorkDay, t2.MissionCode
FROM 
    table1
LEFT JOIN
    (SELECT
        *
     FROM
        table2
     **WHERE id = 1**
     ORDER BY
        WorkDay DESC
     LIMIT 0,1) AS t2
ON
   t2.id = table1.id

对于课程的第一行，它返回ok:

And it returns ok, for the first row ofcourse:

ID | Name | WorkDay    | MissionCode
1    Brain  2019-02-01   2470
2    Amy    NULL         NULL

但是我不能使用

WHERE id = table1.id 

因为MySQL说

Unknown column 'table1.id' in 'where clause'

那么，正确的方法是什么?

So, what is the correct way of this ?

推荐答案

您可以使用简单的JOIN到table2，只需将MAX(WorkDay)条件作为相关子查询放入JOIN条件中即可可以访问table1 id值:

You can use a simple JOIN to table2, just put the MAX(WorkDay) condition into the JOIN condition as a correlated subquery, where you can access the table1 id value:

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.id = t1.id AND
                  t2.WorkDay = (SELECT MAX(WorkDay) 
                                FROM table2 
                                WHERE table2.id = t1.id)

输出:

ID  Name    ID  WorkDay     MissionCode
1   Brain   1   2019-02-01  2470
2   Amy     2   2019-02-01  7210

dbfiddle上的演示

这篇关于MySQL LEFT JOIN仅取决于MAX()值的1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！