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，但我显然得到一组用户和另一组的消息的接收的消息的用户发送。

​​

获取我：

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

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 

Answer 2

我用GREATEST和LEAST创造每一个GRP会话。然后对该grp进行排序并根据时间分配一个行号。

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`); 

Answer 3

最后一次与＃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 
);