mysql - 从包含私有(private)消息的表中选择最新对话

Question

我有一个表，其中包含一个用户发送给另一个用户的个人消息。

这是表结构:

mysql> describe pms;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| time    | datetime     | NO   |     | NULL    |                |
| from    | int(11)      | NO   |     | NULL    |                |
| from_ip | int(11)      | NO   |     | NULL    |                |
| to      | int(11)      | NO   |     | NULL    |                |
| message | varchar(255) | NO   |     | NULL    |                |
| read    | tinyint(4)   | NO   |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+

我正在创建一个 View ，显示特定用户 ID 的 10 个最新对话。由于我想查找对话，所以我想到使用 GROUP BY from, to。然而，这返回了重复的行(既来自该用户又返回给该用户)，并且我还注意到排序无法正常工作。

为了能够正确排序结果并选择 10 个最新对话，组应包含组的最新行而不是第一行。

这是我尝试过的查询:

SELECT * 
FROM  `pms` 
WHERE  `from` = 1
OR  `to` = 1
GROUP BY  `from` ,  `to` 
ORDER BY  `id` DESC 
LIMIT 10

这给出了组中的错误行，因此按 id(或时间)排序给出了错误的顺序。

有什么想法可以让它工作吗？

Answer 1

这假设 session 是由“从-到”对按任意顺序定义的，并且最新的 session 具有最大的 id:

SELECT least(`from`,  `to`), greatest(`from`, `to`)
FROM  `pms` 
WHERE  `from` = 1 OR  `to` = 1
GROUP BY  least(`from`,  `to`), greatest(`from`, `to`)
ORDER BY max( `id`) DESC 
LIMIT 10