SQL：如何按独特的两列组合进行分组？

Question

背景:

• 一张表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 |
+----+-------------------------------------------------+------+---------------------+--------------+------------+

- StoryTeller

---

这些是故意错误的列，我只想要一个用户的消息： WHERE from_user_id=13或to_user_id=13 - StoryTeller

那么对于用户13，您想要与最新帖子的所有对话伙伴（14和15）吗？无论13号是发件人还是收件人？我理解为什么对于15号来说是“哦，你知道了？她怎么样？”但为什么对于14号不是“不能更好！那边的情况怎么样？”呢？ - Thorsten Kettner

你说得对，@Thorsten Kettner，我编辑了问题以展示正确的期望结果。 - StoryTeller

顺便说一下，你对GROUP BY的理解是错误的。它确实可以让你每组获得一条记录（在你的例子中是from_user_id, to_user_id），但它并不会神奇地为你选择记录/值。在SELECT text, from_user_id, to_user_id, created中，你说你想为该组选择一个text。但是有很多个。DBMS可以自由选择一个，并且会任意选择。因此，对于13/14，你可能会得到“嗨！”或“好的，谢谢问候。你怎么样？”；DBMS可以随意选择一个。如果你想要一个特定的，你需要一个聚合函数（MAX、MIN或类似的函数）。 - Thorsten Kettner

然而，你需要的是max(created)的文本 - 这是MySQL不提供的聚合函数（例如Oracle使用KEEP LAST提供此功能，但MySQL没有这样的函数）。 ORDER BY created DESC最后执行，即在GROUP BY之后。 - Thorsten Kettner

Answer 1

您想要的一种方法是使用相关子查询，查找匹配对话的最小创建日期/时间：

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

- Gordon Linoff

---

应该使用MAX()，因为楼主想要最新的消息。 - Ferdinand Gaspar

是的，应该使用 MAX() 函数，但为了公平起见，我在问题中提供了错误的期望结果。 - StoryTeller

Answer 2

最后与 #13 的对话？在更现代的DBMS中，您可以使用 row_number() 来查找这些内容。在MySQL中，您可以使用 not exists，以确保没有关于对话伙伴的更晚的帖子。顺便说一下，您可以使用 from_user_id + to_user_id - 13 轻松找到伙伴的编号。（当比较两条记录时，您只需使用 from_user_id + to_user_id 即可。）

select text, from_user_id, to_user_id, created
from message m1
where 13 in (from_user_id, to_user_id)
and not exists
(
  select *
  from message m2
  where 13 in (m2.from_user_id, m2.to_user_id)
  and m2.from_user_id + m2.to_user_id = m1.from_user_id + m1.to_user_id
  and m2.created > m1.created
);

- Thorsten Kettner

---

你不能比较id的总和，13 + 5 = 8 + 10但它们是不同的对话。 - Juan Carlos Oropeza

@Juan Carlos Oropeza：你缺少了WHERE子句。你的第二组没有包含13，因此不会被选中。 - Thorsten Kettner

Answer 3

我使用 GREATEST 和 LEAST 来为每个对话创建一个组。然后按照该组进行排序，并根据时间分配行号。

SQL DEMO

SELECT *
FROM (
        SELECT LEAST(`from_user_id`, `to_user_id`) as L,
               GREATEST(`from_user_id`, `to_user_id`) as G,
               `text`,
               CONCAT (LEAST(`from_user_id`, `to_user_id`), '-', GREATEST(`from_user_id`, `to_user_id`)) as grp,
               @rn := if(@grp = CONCAT(LEAST(`from_user_id`, `to_user_id`), '-', GREATEST(`from_user_id`, `to_user_id`)),
                         @rn + 1,
                         if(@grp := CONCAT(LEAST(`from_user_id`, `to_user_id`), '-', GREATEST(`from_user_id`, `to_user_id`)), 1, 1)
                         ) as rn,
               `time`
        FROM Table1
        CROSS JOIN (SELECT @rn := 0, @grp := '') as var
        ORDER BY LEAST(`from_user_id`, `to_user_id`),
                 GREATEST(`from_user_id`, `to_user_id`),
                 `time` DESC
     ) T
WHERE rn = 1;

输出

编辑：最后需要从对话中过滤掉13。

WHERE rn = 1
  AND 13 IN (`L`, `G`);

- Juan Carlos Oropeza