SQL:如何按两列的唯一组合分组? [英] SQL: How do I group by a unique combination of two columns?

问题描述

上下文:

• 表message具有列from_user_id和to_user_id
• 用户应查看最近的对话，并显示最后一条消息
• 对话由多条消息组成，这些消息具有相同的用户ID组合(用户发送消息，用户接收消息)

• A table message has the columns from_user_id and to_user_id
• The user should see the recent conversations with the last message displayed
• A conversation consists of multiple messages, that have the same combination of user IDs (user sends messages, user receives messages)

表格内容:

+-------------------------------------------------+--------------+------------+
| 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进行分组，但是显然我得到了用户收到的一组消息和用户发送的另一组消息.

My attempt was to group by from_user_id and to_user_id, but I obviously get a group of the messages received by the user and another group of messages send by the user.

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也不导致所需的结果. 它确实将条目正确分组，但是如您在结果中看到的，最后一条消息不正确.

Using least or greatest does not lead to the required results either. It does group the entries correctly, but as you can see in the result, the last message is incorrect.

+----+-------------------------------------------------+------+---------------------+--------------+------------+
| 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 |
+----+-------------------------------------------------+------+---------------------+--------------+------------+

推荐答案

一种执行所需操作的方法是使用相关子查询，以找到匹配对话的最小创建日期/时间:

One method of doing what you want uses a correlated subquery, to find the minimum created date/time for a matching conversation:

SELECT m.*
FROM message m
WHERE 13 in (from_user_id, to_user_id) AND
      m.created = (SELECT MAX(m2.created)
                   FROM message m2
                   WHERE (m2.from_user_id = m.from_user_id AND m2.to_user_id = m.to_user_id) OR
                         (m2.from_user_id = m.to_user_id AND m2.to_user_id = m.from_user_id) 
                  )
ORDER BY m.created DESC

这篇关于SQL:如何按两列的唯一组合分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！