【MySQL】根据双方的id进行分组并查询每组聊天记录中最新的一条数据

MySQL根据双方的id进行分组并查询每组聊天记录中最新的一条数据

1. 聊天记录表 messages
   
   
2. 根据用户id查询与其聊过天的id
   首先我们需要根据某个用户的id，查询所有与他聊过天的用户id，不论是me_id还是you_id，都需要查询。SQL语句如下（以查询用户id=7为例）：

SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7

查询接收方id为7的发送方id以及发送方id为7的接收方id，并通过UNION取并集，即可得到所有与用户id为7聊过天的用户，查询结果如下：

3. 查询与上述每个id的最新聊天
   上述的结果是单列多行的数据，因此我们可以使用SQL的子查询，通过关键字IN，即查询所有id在上述结果里面的聊天记录，SQL语句如下：

SELECT me_id,you_id,content,time 
FROM messages m 
WHERE 
(m.me_id IN(SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7) AND m.you_id = 7)
OR
(m.me_id=7 AND m.you_id IN(SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7))
ORDER BY time DESC

查询条件有两个，一种是查询id是发送方，另一种是接收方，然后根据消息时间time进行逆向排序，得到最新的数据。查询结果如下：

4. 根据聊天双方id进行分组，返回每组对话的最新消息
   重头戏来了！看到上面的结果，我们首先会想到使用GROUP BY进行分组查询，从而返回每组的最新记录，但事实真的是这样吗？请看：
   
   欸？！为什么返回的数据是最早的记录呢？很奇怪是不是，通过查询资料，我才知道，在MySQL 5.7中，子查询的排序已经变为⽆效了，可能是因为子查询⼤多数是作为⼀个结果给主查询使⽤。因此，我们可以在子查询在使用Limit关键字，对子查询的排序进⾏limit限制，这样子查询就不光是排序，此时排序就会⽣效，但是限制条数我们只能尽可能的设置⼤些，比如999999这种哈哈哈。SQL语句如下：

SELECT me_id,you_id,content,time 
FROM
(SELECT me_id,you_id,content,time 
FROM messages m 
WHERE 
(m.me_id IN(SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7) AND m.you_id = 7)
OR
(m.me_id=7 AND m.you_id IN(SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7))
ORDER BY time DESC LIMIT 999999) msg 
GROUP BY msg.me_id,msg.you_id;

运行结果如下：

可以看到我们得到了每组的最新聊天记录，但是好像还有些问题。。。比如前两行，双方的id都是1和7，这应该属于一组对话，那么怎么合并呢？
根据小学二年级的知识，判断两个数组成的集合是否唯一，只需要保证二者相加和相减的绝对值相同即可，因此分组条件可以写成GROUP BY msg.me_id+msg.you_id,ABS(msg.me_id-msg.you_id);其中ABS表示取绝对值。
完整SQL语句如下：

SELECT me_id,you_id,content,time 
FROM
(SELECT me_id,you_id,content,time 
FROM messages m 
WHERE 
(m.me_id IN(SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7) AND m.you_id = 7)
OR
(m.me_id=7 AND m.you_id IN(SELECT me_id as chat_id FROM messages e WHERE e.you_id = 7 UNION SELECT you_id as chat_id FROM messages e WHERE e.me_id = 7))
ORDER BY time DESC LIMIT 999999) msg 
GROUP BY msg.me_id+msg.you_id,ABS(msg.me_id-msg.you_id);

结果如下：

-------------------------------分割线 2022年8月24日更-----------------------------------------

今天评论区有位朋友提了一个极好的问题：如果上述id是字符串要怎么处理呢？

前面我们讲的都是int类型的id，最后的分组查询可以使用『二者相加和相减的绝对值相同』这一条件进行判断。但有时候用户id是字符串类型，不能直接相加减怎么办呢。

我本来的想法是运用集合，众所周知，集合{abc，def}和集合{def,abc}是等价的，也就是说集合中元素的顺序不影响集合的含义，但是使用SQL对集合进行判断处理。。。。恕我学艺不精，没有找到很好的方法。but。。。

根据我们小学三年级的知识，字符有对应的ASCII码，而ASCII码不就相当于数字吗，不就可以相加减了吗？！哈哈哈功夫不负有心人，经过试验，结论正确。

过程如下：

1. 首先重新创建一个表，me_id和you_id数据类型设置为char字符型
   

2. 然后查询与用户id为“test”聊过天用户的所有消息记录

SELECT me_id,you_id,content,time
FROM
(SELECT me_id,you_id,content,time 
FROM message m 
WHERE 
(m.me_id IN(SELECT me_id as chat_id FROM message e WHERE e.you_id = 'test' UNION SELECT you_id as chat_id FROM message e WHERE e.me_id = 'test') AND m.you_id = 'test')
OR
(m.me_id='test' AND m.you_id IN(SELECT me_id as chat_id FROM message e WHERE e.you_id = 'test' UNION SELECT you_id as chat_id FROM message e WHERE e.me_id = 'test'))
ORDER BY time DESC LIMIT 999999) msg 

查询结果如下：

3. 接着进行分组查询，查询条件为me_id、you_id的ASCII码相加和相减绝对值相同

SELECT me_id,you_id,content,time
FROM
(SELECT me_id,you_id,content,time 
FROM message m 
WHERE 
(m.me_id IN(SELECT me_id as chat_id FROM message e WHERE e.you_id = 'test' UNION SELECT you_id as chat_id FROM message e WHERE e.me_id = 'test') AND m.you_id = 'test')
OR
(m.me_id='test' AND m.you_id IN(SELECT me_id as chat_id FROM message e WHERE e.you_id = 'test' UNION SELECT you_id as chat_id FROM message e WHERE e.me_id = 'test'))
ORDER BY time DESC LIMIT 999999) msg 
GROUP BY ASCII(msg.me_id)+ASCII(msg.you_id),ABS(ASCII(msg.me_id)-ASCII(msg.you_id));

这样我们就得到了每组的最新聊天记录，并且进行了去重，即发送和接收双方的最新一条的消息，结果如下：

吾生也有涯，其知也无涯~

OK！大功告成~