mysql - 选择与 Groupby 中的条件匹配的第一行

Question

给定以下消息表，其中 channel 是特定的聊天 session ，user1 和 user2 是聊天中的用户:

+---------+-------+-------+------------+
| channel | user1 | user2 |  message   |
+---------+-------+-------+------------+
|       5 |    15 |     8 | Hello      |
|       5 |    15 |     8 | I'm John   |
|       5 |     8 |    15 | Hi John    |
|       6 |     9 |    15 | yo         |
|       6 |    15 |     9 | heyo       |
|       6 |     9 |    15 | you here?  |
|       8 |    15 |    10 | Hi         |
|       8 |    15 |    10 | you there? |
+---------+-------+-------+------------+

我想按 channel 进行分组并选择第一个响应行(第二个人说了某事的第一行)。如果第二个人从未像 channel 8 中那样做出响应，则他们不需要出现在输出中。

所以我的预期输出是这样的:

+---------+-------+-------+---------+
| channel | user1 | user2 | message |
+---------+-------+-------+---------+
|       5 |     8 |    15 | Hi John |
|       6 |    15 |     9 | heyo    |
+---------+-------+-------+---------+

请注意，有一个时间戳列，只是忘记包含它。任何帮助将不胜感激，一直在寻找解决方案，但尚未提出任何解决方案。谢谢。

Answer 1

假设您有时间戳列，您可以获取第一条消息的user2，如下所示:

select m.*
from messages m
where not exists (select 1
                  from messages m2
                  where m2.channel = m.channel and
                        m2.timestamp < m.timestamp
                 );

因此，如果您想要其中的第一条消息，可以使用 group_concat()/substring_index()` 技巧:

select m.channel, m.user1, m.user2,
       substring_index(group_concat(m2.messages order by m2.timestemp separator '|'), '|', 1)
from messages m join
     (select m.*
      from messages m
      where not exists (select 1
                        from messages m2
                        where m2.channel = m.channel and
                              m2.timestamp < m.timestamp
                       )
     ) mfirst
     on m.channel = mfirst.channel and
        m.user1 = mfirst.user2
group by m.channel, m.user1, m.user2;