MySQL SQL 在 LEFT JOIN 上获取最大 id 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14678056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL getting max id field on a LEFT JOIN
提问by Steven
I am trying to pull the photo from tblimage that corresponds to the maxid in the tblimage for each user. Currently, I am getting all the messages from the message table and a random photo for the user that posted the message, I would like the photo to be the latest uploaded photo. the way its written now it just pulls a random photo from the table. any suggestions?
我正在尝试从与每个用户的 tblimage 中的 maxid 相对应的 tblimage 中提取照片。目前，我正在从消息表中获取所有消息以及发布消息的用户的随机照片，我希望照片是最新上传的照片。现在它的书写方式只是从桌子上随机抽取一张照片。有什么建议？
table structures are as such:
表结构如下：
messages: msgid, message, user_id, event_id
tblimage: id, photo, userid
消息：msgid、消息、user_id、event_id
tblimage：id、照片、userid
SELECT messages.*, tblimage.photo, max(tblimage.id)
FROM messages LEFT JOIN tblimage ON messages.user_id = tblimage.userid
GROUP BY messages.msg_id, messages.user_id
ORDER BY messages.msg_id DESC, tblimage.id desc
回答by Terje D.
Try
尝试
SELECT messages.*, T2.photo
FROM messages
LEFT JOIN (SELECT userid, MAX(id) AS maxid
FROM tblimages
GROUP BY userid) AS T1
ON messages.user_id = T1.userid
LEFT JOIN tblimages AS T2
ON T2.id = T1.maxid
ORDER BY messages.msg_id DESC
which finds max(id) for each user in tblimages, then uses that to join each user to the latest photo for that user.
它在 tblimages 中为每个用户找到 max(id)，然后使用它来将每个用户加入该用户的最新照片。
