从INNER JOIN中选择最后一条记录并进行分组 [英] Selecting last record from INNER JOIN and grouping
本文介绍了从INNER JOIN中选择最后一条记录并进行分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
好的,所以我有一个查询,该查询从按日期DESC排序的每个用户中选择1条消息,但为每个组选择第一条消息.我需要按日期从每个组(每个用户)中选择最后一条消息 这是我的查询:
Okay so I have a query that selects 1 message from each user that is ordered by date DESC but selects the first message for each group.. I need to select the last message by date from each group (each user) This is my query:
SELECT people.id,
people.avatar,
people.firstname,
people.lastname,
LEFT(messages.message, 90) AS message,
messages.seen,
messages.date
FROM people
INNER JOIN messages
ON people.id = messages.sender_id
WHERE reciever_id = '". $user_data['id'] ."'
GROUP BY sender_id
ORDER BY date DESC limit 11
推荐答案
请稍等一下,请原谅我的语法,但是我可能会尝试这样的方法.
Please excuse my syntax if it is a bit off, but I would probably try something like this.
SELECT people.id,
people.avatar,
people.firstname,
people.lastname,
LEFT(msg.message, 90) AS message,
msg.seen,
msg.date
FROM people
INNER JOIN (SELECT message, seen, date, sender_id
FROM messages GROUP By sender_id
ORDER By date DESC LIMIT 1) msg
ON people.id = msg.sender_id
WHERE reciever_id = '". $user_data['id'] ."'
这篇关于从INNER JOIN中选择最后一条记录并进行分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文