背景:
- 一张表
message
有列from_user_id
和to_user_id
- 用户应该能够看到最近的对话,并显示最后一条消息
- 一个对话由多条消息组成,这些消息具有相同的用户ID组合(用户发送消息,用户接收消息)
表格内容:
+-------------------------------------------------+--------------+------------+
| text | from_user_id | to_user_id |
+-------------------------------------------------+--------------+------------+
| Hi there! | 13 | 14 | <- Liara to Penelope
| Oh hi, how are you? | 14 | 13 | <- Penelope to Liara
| Fine, thanks for asking. How are you? | 13 | 14 | <- Liara to Penelope
| Could not be better! How are things over there? | 14 | 13 | <- Penelope to Liara
| Hi, I just spoke to Penelope! | 13 | 15 | <- Liara to Zara
| Oh you did? How is she? | 15 | 13 | <- Zara to Liara
| Liara told me you guys texted, how are things? | 15 | 14 | <- Zara to Penelope
| Fine, she's good, too | 14 | 15 | <- Penelope to Zara
+-------------------------------------------------+--------------+------------+
我的尝试是按照
from_user_id
和to_user_id
分组,但我显然得到了用户接收的消息组和用户发送的消息组。SELECT text, from_user_id, to_user_id,created FROM message
WHERE from_user_id=13 or to_user_id=13
GROUP BY from_user_id, to_user_id
ORDER BY created DESC
明白了:
+-------------------------------+--------------+------------+---------------------+
| text | from_user_id | to_user_id | created |
+-------------------------------+--------------+------------+---------------------+
| Oh you did? How is she? | 15 | 13 | 2017-09-01 21:45:14 | <- received by Liara
| Hi, I just spoke to Penelope! | 13 | 15 | 2017-09-01 21:44:51 | <- send by Liara
| Oh hi, how are you? | 14 | 13 | 2017-09-01 17:06:53 |
| Hi there! | 13 | 14 | 2017-09-01 17:06:29 |
+-------------------------------+--------------+------------+---------------------+
虽然我想要:
+-------------------------------+--------------+------------+---------------------+
| text | from_user_id | to_user_id | created |
+-------------------------------+--------------+------------+---------------------+
| Oh you did? How is she? | 15 | 13 | 2017-09-01 21:45:14 | <- Last message of conversation with Zara
| Oh hi, how are you? | 14 | 13 | 2017-09-01 17:06:53 |
+-------------------------------+--------------+------------+---------------------+
我该怎么做才能实现这个目标?
编辑: 使用
least
或 greatest
也无法得到所需结果。
它确实正确地分组了条目,但是如你在结果中所看到的,最后一条消息是不正确的。+----+-------------------------------------------------+------+---------------------+--------------+------------+
| id | text | read | created | from_user_id | to_user_id |
+----+-------------------------------------------------+------+---------------------+--------------+------------+
| 8 | Oh you did? How is she? | No | 2017-09-01 21:45:14 | 15 | 13 |
| 5 | Could not be better! How are things over there? | No | 2017-09-01 17:07:47 | 14 | 13 |
+----+-------------------------------------------------+------+---------------------+--------------+------------+
WHERE from_user_id=13或to_user_id=13
- StoryTellerGROUP BY
的理解是错误的。它确实可以让你每组获得一条记录(在你的例子中是from_user_id, to_user_id
),但它并不会神奇地为你选择记录/值。在SELECT text, from_user_id, to_user_id, created
中,你说你想为该组选择一个text
。但是有很多个。DBMS可以自由选择一个,并且会任意选择。因此,对于13/14,你可能会得到“嗨!”或“好的,谢谢问候。你怎么样?”;DBMS可以随意选择一个。如果你想要一个特定的,你需要一个聚合函数(MAX
、MIN
或类似的函数)。 - Thorsten Kettnermax(created)
的文本 - 这是MySQL不提供的聚合函数(例如Oracle使用KEEP LAST
提供此功能,但MySQL没有这样的函数)。ORDER BY created DESC
最后执行,即在GROUP BY
之后。 - Thorsten Kettner