mysql - 按 "thread"或 "discussion"排序消息表

Question

我正在写聊天记录，让我们称“thread”为两个用户的讨论。
我需要订购这个:

        from_id |   to_id | message_text | send_time
----------------+---------+--------------+--------------------
             24 |      25 |some text     | 2016-02-27 18:48:26
             36 |      34 |some text     | 2016-03-02 16:40:03
             24 |       1 |some text     | 2016-03-10 17:02:56
             83 |      84 |some text     | 2016-03-16 11:58:27
             84 |      83 |some text     | 2016-02-27 18:48:26
             83 |      84 |some text     | 2016-03-16 12:02:57
             25 |      24 |some text     | 2016-02-27 18:48:26
             84 |      83 |some text     | 2016-03-16 12:03:30

变得像

        from_id |   to_id | message_text | send_time
----------------+---------+--------------+--------------------
             24 |      25 |some text     | 2016-02-27 18:48:26
             25 |      24 |some text     | 2016-02-27 18:48:26 
             24 |       1 |some text     | 2016-03-10 17:02:56
             83 |      84 |some text     | 2016-03-16 11:58:27
             84 |      83 |some text     | 2016-02-27 18:48:26
             83 |      84 |some text     | 2016-03-16 12:02:57
             84 |      83 |some text     | 2016-03-16 12:03:30
             36 |      34 |some text     | 2016-03-02 16:40:03 

行必须按时间排序聚合线程和每个线程

Answer 1

当相同的两个用户进行通信时，您将其称为线程，无论日期和时间是什么以及间隔多长时间。因此，您只需寻找相同的合作伙伴即可找到话题。线程的键可以是least(from_id, to_id), greatest(from_id, to_id)。使用他们的第一次通信时间作为排序键。然后再次加入您的餐 table ，整理您的记录。

select m.*
from
(
  select 
    least(from_id, to_id) as lesser_id, 
    greatest(from_id, to_id) as greater_id, 
    min(send_time) as sortkey
  from mytable
  group by least(from_id, to_id), greatest(from_id, to_id)
) thread
join mytable m on least(m.from_id, m.to_id) = thread.lesser_id
               and greatest(m.from_id, m.to_id) = thread.greater_id
order by thread.sort_key, thread.lesser_id, thread.greater_id, m.send_time;