我在MySQL数据库中具有以下2个表A和B。当我通过VisitID（A.VisitID = B.VisitID）加入2个表时，我只想从表B中为VisitID提取与最新的Actiondateandtime相关的记录。例如，VisitID = 85，我想在表B中使用15/10/2014 3:44提取行。对于VisitID = 86，我只想在表B中使用09/10/2014 1:28提取行。
表A：
VisitID VisitTitle VisitSummary Conclusion
85 Go to Paddy Field 1 Checked Temperatures 1
86 Soil Quality Checked Checked PHP of different soil 2
87 Go to Paddy Field 2 Collected Soil samples 0
表B：
RefID VisitID ActionDesc Actiondateandtime
1 85 Submiited to Management 9/10/2014 12:03
2 86 Sent to lab 9/10/2014 1:06
3 86 Sent to lab 9/10/2014 1:07
4 86 Sent to lab 9/10/2014 1:21
5 86 Sent to lab 9/10/2014 1:28
6 87 Followed with Soil scientist 9/10/2014 1:32
7 87 Followed with Soil scientist 9/10/2014 1:33
8 85 Submitted to Management 15/10/2014 3:44
我想要这两个表联接后的结果
A.VisitID A.VisitTitle A.Conclusion B.RefID B.VisitID B.Actiondateandtime
85 Go to Paddy Field 1 1 8 85 15/10/2014 3:44
86 Soil Quality Checked 2 5 86 9/10/2014 1:28
87 Go to Paddy Field 2 0 7 87 9/10/2014 1:33
要获得此所需结果，需要哪些MySQL代码？
最佳答案
一种选择是使用join集合将表max还原回自身，以获取按visitid分组的最大日期：
select a.visitid, a.visittitle, a.conclusion, b.refid, b.actiondateandtime
from tablea a
join tableb b on a.visitid = b.visitid
join (
select visitid, max(actiondateandtime) actiondateandtime
from tableb
group by visitid
) c on b.visitid = c.visitid and b.actiondateandtime = c.actiondateandtime
SQL Fiddle Demo
关于mysql - 在加入2个mysql表时，如何仅获取第二个表中所需的相关行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27455888/