我正在构建一个聊天系统，用户 A 可以与用户 B 讨论主题 X 或 Y。
我的表如下所示：
Message ID | message | sender | receiver | topic
1 Helo 1 2 X
2 Lorem 2 1 X
3 Foo 1 2 Y
4 Bar 2 1 X
5 Test 2 1 Y
6 Hello2 1 3 X
因此，我想获取每个用户和主题的用户 1 的第 6 个最后对话的列表。目前我得到了
SELECT messageid, sender,topic, receiver AS friend FROM table WHERE sender = 1 || receiver = 1 GROUP BY topic,friend ORDER BY id DESC LIMIT 0,6
但这不会选择最后一条消息。因为之后我创建了一个这些条目的列表，并且不想在顶部显示最后的对话......
在此示例中，我需要如下所示的列表：
Message ID | sender | topic| friend
6 1 X 3
5 2 Y 1
4 2 X 1
通过以下选择，它可以工作！ select * from (select Message_ID,sender,topic, receiver AS friend from tab where sender = 1 || receiver = 1
order by Message_ID desc) temp
group by topic order by Message_ID desc
工程！
如果需要给定用户的最后一个消息 ID 的 ID：
select (case when sender = 1 then receiver else sender end) as other,
topic, max(messageid)
from messages
where sender = 1 or receiver = 1
group by (case when sender = 1 then receiver else sender end) ;
然后，您可以使用它来获取所有信息：
select m.*
from messages m join
(select (case when sender = 1 then receiver else sender end) as other,
topic, max(messageid) as messageid
from messages
where sender = 1 or receiver = 1
group by (case when sender = 1 then receiver else sender end)
) mm
on mm.messageid = m.messageid;
我想
，问题是在group by子句上选择了第一行。在对它们进行分组之前order by可以使用子句轻松解决。
select * from
(select Message_ID,sender,topic, receiver AS friend from tab
where sender = 1 || receiver = 1
order by Message_ID desc) temp
group by friend order by Message_ID desc
请在 http://sqlfiddle.com/#!9/bf53d/10 验证答案