mysql - 只返回最后一行 LEFT JOIN

Question

SELECT DISTINCT msg.userid, msg.messages, user.fullname, prof.path
FROM messages AS msg
LEFT JOIN users AS user ON msg.userid = md5( user.userid )
LEFT JOIN profile AS prof ON msg.userid = prof.userid
ORDER BY msg.date ASC
LIMIT 0 , 30

上面的代码可以工作，但问题是结果有重复值:

userid | messages | fullname | path
985434 | hello... | Foo Bar  | /path/to/hello.jpg
985434 | hello... | Foo Bar  | /path/to/new.jpg

问题是 PATH。 如何限制path的结果为最近的？还是每个全名只有一个？...这让我很痛苦感谢您的理解。

Answer 1

您可以通过使用 GROUP_CONCAT() 所有路径并在组上使用 SUBSTRING_INDEX() 来选择第一条路径，您也可以在 中使用 order by >GROUP_CONCAT( prof.path order by some_col DESC ) 喜欢

SELECT 
 msg.userid,
 msg.messages, 
 user.fullname, 
SUBSTRING_INDEX(GROUP_CONCAT( prof.path ),',',1) AS path
FROM messages AS msg
LEFT JOIN users AS USER ON msg.userid = MD5( user.userid )
LEFT JOIN profile AS prof ON msg.userid = prof.userid
GROUP BY msg.userid
ORDER BY msg.date ASC
LIMIT 0 , 30