mysql - 在 MySQL 中聊天 - 选择 - GROUP BY

Question

我有这些表:

表名称:user_data

---------------------------
| id | owner |    name    |
---------------------------  
|  1 |  120  |  "Nicolas" |   
|  2 |  140  |   "Angel"  |
---------------------------

表名称:private_messages

--------------------------------------------
| id | ownerID | toUserID | message        |
--------------------------------------------  
|  1 |   120   |   140    | "Hi"           |
|  2 |   120   |   140    | "How are you?" |
|  3 |   120   |   140    | "I miss u"     |
|  4 |   140   |   120    | "Viewed."      |
--------------------------------------------

我需要选择某个用户的所有聊天行，例如，我有用户 ID“120”，我需要进行查询以返回在ownerID或toUserID中显示“120”的所有聊天。

这是我的实际查询:

SELECT
user_data.owner,
ANY_VALUE(user_data.name) name,
ANY_VALUE(private_messages.message) message
FROM private_messages 
INNER JOIN user_data ON private_messages.ownerID = user_data.owner 
WHERE private_messages.owner = "120" OR private_messages.toUserID = "120"
GROUP BY user_data.owner

此查询的结果是:

----------------------------------------
| owner |    name     |    message     |
----------------------------------------  
|  120  |  "Nicolas"  |     "Hi"       |
|  140  |   "Angel"   |   "Viewed."    |
----------------------------------------

但是如果您认为这是错误的，因为这两个元素来自同一个聊天。正确的结果应该是两个用户之间的最后一条消息:

----------------------------------------
| owner |    name     |    message     |
----------------------------------------
|  140  |   "Angel"   |   "Viewed."    |
----------------------------------------

有办法实现吗？

Answer 1

SELECT
user_data.owner,
ANY_VALUE(user_data.name) name,
ANY_VALUE(private_messages.message) message
FROM private_messages 
INNER JOIN user_data ON private_messages.owner = user_data.owner 
WHERE private_messages.touser = "120"
GROUP BY user_data.owner