当列中的值互换时，MySQL用2列分组 [英] MySQL group by with 2 columns when values are interchanged in columns

问题描述

请参阅此表.它包含带有传入和传出消息的聊天记录

See this table. It contains a chat log with incoming and outgoing messages

    id | to_number    | from_number  |message         | direction| type   |  generated_time
     1 | [phone] | [phone] |Hai             | OUTGOING |        | 2019-07-12 15:13:00
     2 | [phone] | [phone] |Hello           | INCOMING | REVIEW | 2019-07-13 18:13:00
     3 | [phone] | [phone] |How are you ?   | OUTGOING |        | 2019-07-13 21:15:00
     4 | [phone] | [phone] |Good morning    | OUTGOING |        | 2019-07-12 12:13:00
     5 | [phone] | [phone] |Have a nice day | INCOMING | REVIEW | 2019-07-12 12:17:00
     6 | [phone] | [phone] |Hey John        | OUTGOING |        | 2019-07-12 14:13:00

现在，我想在每个线程中获取最后生成的消息，在这些线程中，我们收到了带有REVIEW类型的传入消息. 查看所需的输出

Now i want to get the last generated message in each threads in which we got an incoming message with type REVIEW. See the desired output

    to_number    | from_number  |message         | direction| type   | generated_time
    [phone] | [phone] |How are you ?   | OUTGOING |        | 2019-07-13 21:15:00
    [phone] | [phone] |Have a nice day | INCOMING | REVIEW | 2019-07-12 12:17:00

我尝试查询

    select *
    from (select b.*
          from (select *
                from message m
                where m.id in (select min(msg.id)
                        from message msg
                        where msg.direction = 'INCOMING'
                        group by msg.from_number, msg.to_number)
                  and m.type = 'REVIEW') a
                 left join (select * from message m) b 
                  on ((a.from_number = b.to_number and a.to_number = b.from_number) or
                      (a.from_number = b.from_number and a.to_number = b.to_number))
          order by b.generated_time desc
    ) c group by from_number,to_number order by generated_time desc;

但是它没有正确地对消息进行分组. 有人可以帮我找到解决方案吗?

But it is not grouping the messages properly. Can anybody help me to find a solution for this ?

推荐答案

确定每行线程"的一种方法是

One way to determine the "thread" for each row is CONCAT() the LEAST of the two numbers with the GREATEST of the same two numbers.

然后我们可以在线程"上按GROUP BY，以获取最新的generated_time.在HAVING子句中，我们仅过滤掉那些至少包含一条'REVIEW'类型的'INCOMING'消息的线程".

We can then GROUP BY on the "thread", to get the latest generated_time. In HAVING clause, we filter out only those "thread", which has atleast one 'INCOMING' message with 'REVIEW' type.

在DB Fiddle上查看

SELECT m1.*
FROM   message AS m1
       JOIN (SELECT Concat(Least(m.from_number, m.to_number), '|',
                                                      Greatest(m.from_number,
                                                      m.to_number))
                                              AS
                    thread,
                    Max(m.generated_time)
                                              AS max_generated_time
             FROM   message AS m
             GROUP  BY thread
             HAVING Sum(m.direction = 'INCOMING'
                        AND m.type = 'REVIEW')) AS dt
         ON dt.thread = Concat(Least(m1.from_number, m1.to_number), '|',
                                         Greatest(m1.from_number, m1.to_number))
            AND dt.max_generated_time = m1.generated_time;

结果

| id  | to_number    | from_number  | message         | direction | type   | generated_time      |
| --- | ------------ | ------------ | --------------- | --------- | ------ | ------------------- |
| 3   | [phone] | [phone] | How are you ?   | OUTGOING  |        | 2019-07-13 21:15:00 |
| 5   | [phone] | [phone] | Have a nice day | INCOMING  | REVIEW | 2019-07-12 12:17:00 |

---

边注:

1. 上述方法(和您当前的方案设计)无法使用索引，因此不会表现出色.
2. 我宁愿通过创建两个其他主表来重新设计架构.一个主表将存储电话号码:phone_id和number
3. 另一个主表将存储"Thread"，其中将包含phone_id值和thread_id.然后，您可以在message表中使用此thread_id，而不是存储电话号码.

1. Above approach (and your current schema design) is not able to use indexes, and hence it will not be performant.
2. I would rather redesign the schema by creating two additional Master tables. One Master table would be storing the phone numbers: phone_id, and number
3. Another Master table would be storing the "Thread", which will contain the phone_id values and thread_id. You can then use this thread_id in your message table, instead of storing the phone numbers.

这篇关于当列中的值互换时，MySQL用2列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！