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
这篇关于MySQL LEFT JOIN仅取决于MAX()值的1行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!