SQL在左联接上获取最大ID字段 [英] SQL getting max id field on a LEFT JOIN
问题描述
我正在尝试从tblimage中提取与每个用户的tblimage中的maxid相对应的照片.目前,我正在从消息表中获取所有消息,并为发布消息的用户提供了一张随机照片,我希望该照片是最新上传的照片.按照现在的书写方式,它只是从桌子上拉出一张随机照片.有什么建议?
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?
表结构如下:
消息:msgid,消息,user_id,event_id
tblimage:ID,照片,用户ID
messages: msgid, message, user_id, event_id
tblimage: id, photo, 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
推荐答案
尝试
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
会在tblimage中为每个用户找到max(id),然后使用该ID将每个用户加入该用户的最新照片.
which finds max(id) for each user in tblimages, then uses that to join each user to the latest photo for that user.
这篇关于SQL在左联接上获取最大ID字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!