mysql left join 只要一条,MySQL LEFT JOIN仅取决于MAX（）值的1行

Table 1

ID | Name

1 Brain

2 Amy

Table 2:

ID | WorkDay | MissionCode

1 2019-01-01 2360

1 2019-02-01 2470

2 2019-01-01 4470

2 2019-02-01 7210

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:

Expected Result

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

But it returns NULL values from table2, like this:

ID | Name | WorkDay | MissionCode

1 Brain NULL NULL

2 Amy NULL NULL

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

And it returns ok, for the first row ofcourse:

ID | Name | WorkDay | MissionCode

1 Brain 2019-02-01 2470

2 Amy NULL NULL

But I can't use

WHERE id = table1.id

because MySQL says

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

So, what is the correct way of this ?

解决方案

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)

Output:

ID Name ID WorkDay MissionCode

1 Brain 1 2019-02-01 2470

2 Amy 2 2019-02-01 7210