我有一个类似以下的数据库表(我知道设计不好,但是有很多这样的行):

person1    |    person2     |    counselor
Jane Doe   |    John Doe    |    Mary Smith
John Doe   |    Jane Doe    |    Mary Smith
Frank Jones|    Ann Jones   |    Tom Jones
Ann Jones  |    Frank Jones |    Tom Jones


我试图弄清楚如何只选择“唯一”行之一,以便结果看起来像:

person1    |    person2     |    counselor
Jane Doe   |    John Doe    |    Mary Smith
Frank Jones|    Ann Jones   |    Tom Jones


我已经尝试过诸如SELECT distinctSELECT MIN(person1)之类的各种方法,但是很引人注目。

最佳答案

您将有6个(person1,person2,counselor)排列,并且您可以对所有这些使用union。最后使用where子句,这样每个组合仅返回一行。

Fiddle with sample data

select * from (
select person1,person2,counselor
from tablename
union
select person1,counselor,person2
from tablename
union
select person2,person1,counselor
from tablename
union
select person2,counselor,person1
from tablename
union
select counselor,person2,person1
from tablename
union
select counselor,person1,person2
from tablename) t
where person1 < person2 and person2 < counselor

10-07 15:15