请帮助我检索每个会话的第一条消息。
表：消息
id | sender_id | recipient_id | content | Created_at
1 | 100 | 101 | AB | 2017-03-20 22:37:30
2 | 101 | 100 | Hello | 2017-03-21 20:29:10
3 | 101 | 100 | XYZ | 2017-03-21 22:02:00
4 | 200 | 100 | xxt | 2017-03-21 23:01:01
5 | 200 | 100 | Hi there | 2017-03-22 10:10:10
6 | 102 | 500 | Can you meet?| 2017-03-22 10:15:32
7 | 600 | 700 | Yes | 2017-03-22 10:20:30
8 | 600 | 700 | Hello world | 2017-03-23 01:00:00
9 | 700 | 600 | Hi | 2017-03-23 08:10:10
10 | 800 | 900 | hello | 2017-03-26 22:00:00
预期结果：
id | sender_id | recipient_id | content | Created_at
1 | 100 | 101 | AB | 2017-03-20 22:37:30
4 | 200 | 100 | xxt | 2017-03-21 23:01:01
6 | 102 | 500 | Can you meet?| 2017-03-22 10:15:32
7 | 600 | 700 | Yes | 2017-03-22 10:20:30
10 | 800 | 900 | hello | 2017-03-26 22:00:00
提前谢谢你。
最佳答案
尝试使用带限制的相关子查询来找出给定两个人之间第一次对话的id。
select *
from your_table t
where id = (
select id
from your_table t2
where least(t.sender_id, t.recipient_id) = least(t2.sender_id, t2.recipient_id)
and greatest(t.sender_id, t.recipient_id) = greatest(t2.sender_id, t2.recipient_id)
order by id limit 1
);
Demo