即使切换，MYSQL也会区分两列的输入 [英] MYSQL distincs entry of two columns even when switched

问题描述

我的mysql表具有以下结构:

my mysql table has the following structure:

ID    Sender    Recipient    Text
--    ------    ---------    ----
 1    10        11           Text
 2    10        11           Text
 3    11        10           Text
 4    11        10           Text
 5    12        13           Text
 6    13        12           Text
 7    14        15           Text
 8    14        15           Text
 9    17        16           Text
10    16        17           Text

此表表示两个用户之间的私人消息对话.我现在想要一个列表，让我仅显示每个聊天一次.通常，在这种情况下，我会和一个小组一起去，但是然后我仍然有一个问题，因为发件人和收件人列也可能是相反的情况.

This table represents a private message conversation between two users. I now would like a list were i show each chat only once. Normally i would go with a group by in this case but then i still have a problem because the sender and recipient column can also be the other way around.

我想收到这样的结果:

ID    Sender    Recipient    Text
--    ------    ---------    ----
 1    10        11           Text
 2    12        13           Text
 3    14        15           Text
 4    16        17           Text

因此每个对话都是唯一的. 有谁知道如何解决这个问题?

So each of the conversations would be unique. Does anyone have an idea how to solve this?

推荐答案

您可以使用如下查询:

select
  min(id) as id,
  least(sender,recipient) as sender,
  greatest(sender,recipient) as recipient
  min(text) as text
from
  tablename
group by
  least(sender,recipient),
  greatest(sender,recipient)

(或者您可以对文本使用另一个聚合函数，例如group_concat，或者您必须两次连接表名才能获得聊天的第一个或最后一个消息，但这取决于要返回的内容)

(or you can use another aggregated function for the text, like group_concat, or you have to join tablename twice to get the first or the last msg of the chat, but this depends on what you want to return)

这篇关于即使切换，MYSQL也会区分两列的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！