sql - 同时按两列分组，分组方式匹配col1,col2和col2,col1

Question

我有一个包含 sender_id、recipient_id 和 message 列的“聊天”表:

sender_id | recipient_id | message
-------------------------------------
     1    |      2       | Test 1
     2    |      1       | test test
     1    |      3       | more tests
     1    |      2       | aaaa

我想按 (sender_id, recipient_id) 和反向 (recipient_id, sender_id) 对结果进行分组。结果应如下所示:

sender_id | recipient_id
------------------------
     1    |      2
     1    |      3

这是因为发件人也可以是另一行的收件人，我不想要这样的结果

sender_id | recipient_id
------------------------
     1    |      2
     2    |      1

这是工作正常但有一些缺陷的 SQL:

SELECT DISTINCT ON sender_id+recipient_id, sender_id, recipient_id 
FROM chat 
WHERE (sender_id = 10 AND recipient_id = 10);

缺陷: 1. 我不能在我的代码中使用 DISTINCT ON。 2. 它会排成一排(我不确定它是否真的会发生)像这样:

sender_id | recipient_id
------------------------
    10    |      5
     9    |      6

我不知道如何解决这个问题。任何帮助表示赞赏。

Answer 1

您可以使用 least() 和 greatest() 来首先获取较低的 ID，最后获取较高的 ID(如果您愿意，反之亦然)。

使用DISTINCT:

SELECT DISTINCT
       least(sender_id, recipient_id) least_id,
       greatest(sender_id, recipient_id) greatest_id
       FROM chat;

或者使用 GROUP BY，如果您真的想聚合某些东西(无论是什么，max(message)？):

SELECT least(sender_id, recipient_id) least_id,
       greatest(sender_id, recipient_id) greatest_id,
       ...
       FROM chat
            GROUP BY least(sender_id, recipient_id),
                     greatest(sender_id, recipient_id);