MySQL中两列的DISTINCT值

I have a table with contents like the following:

我有一个包含以下内容的表:

+----+-----------+---------+--------+------+
| id | text_from | text_to |  text  | seen |
+----+-----------+---------+--------+------+
|  1 | A         | B       | Hello1 |    0 |
|  2 | X         | Y       | Hello2 |    1 |
|  3 | Y         | X       | Hello3 |    1 |
|  4 | B         | A       | Hello4 |    1 |
+----+-----------+---------+--------+------+

It is a conversation like A sends a text to B, B sends to A etc. How can I get the DISTINCT conversation? For example, distinct conversation between A and B, or X and Y etc.

这是一个对话,比如A向B发送文本,B向A发送等等。我怎样才能得到DISTINCT对话?例如,A和B之间,或X和Y等之间的不同对话。

I want to get something like

我希望得到类似的东西

+----+-----------+---------+--------+------+
| id | text_from | text_to |  text  | seen |
+----+-----------+---------+--------+------+
|  1 | A         | B       | Hello1 |    0 |
|  2 | X         | Y       | Hello2 |    1 |
+----+-----------+---------+--------+------+

If once text_from and text_to has two unique values, it can not be repeated. For example, if there is text_from = A, text_to = B, the table should not have text_from = B, text_to = A.

如果text_from和text_to有一个唯一值,则无法重复。例如,如果有text_from = A,text_to = B,则表不应该有text_from = B,text_to = A.

I am trying several methods for DISTINCT and GROUP BY since a few hours, but could not figure out any solution! Any suggestions would be greatly appreciated.

几个小时后我正在为DISTINCT和GROUP BY尝试几种方法,但无法找出任何解决方案!任何建议将不胜感激。

1 个解决方案

#1

Seems like a simple NOT EXISTS should do the trick. Example SQL Fiddle

看起来像一个简单的NOT EXISTS应该做的伎俩。示例SQL小提琴

select *
from table t
where not exists (
  select 1
  from table t1
  where
    (
      (t.text_from = t1.text_from
       and t.text_to = t1.text_to)
      or (t.text_from = t1.text_to
       and t.text_to = t1.text_from)
    ) and t.id > t1.id
  )