从表联接中选择不同的有序对 [英] Select distinct ordered pair from table join

问题描述

请考虑两个带有名称的表.它们由表A ID连接在一起，以便使两个名称相关联.
是否有一个MySQL查询返回不重复的名称对?

Please consider two tables with names. They are joined by Table A id, so that two names get associated.
Is there a MySQL query that returns distinct pair of names regardless the order?

第一张桌子:

table_a
+-----------+--------------+
|    id     |     name     |
+-----------+--------------+
|     1     |     John     |
+-----------+--------------+
|     2     |     Jane     |
+-----------+--------------+ 
|     3     |     Jane     |
+-----------+--------------+
|     4     |     Sammy    |
+-----------+--------------+    

第二张表:

table_b
+-----------+-------------------+-------------+
|    id     |     id_table_a    |    name     |
+-----------+-------------------+-------------+
|    1      |          1        |    Jane     |
+-----------+-------------------+-------------+
|    2      |          2        |    John     |
+-----------+-------------------+-------------+
|    3      |          3        |    Sammy    |
+-----------+-------------------+-------------+
|    4      |          4        |    Tara     |
+-----------+-------------------+-------------+

所需结果

(John, Jane)
(Jane, Sammy)  
(Sammy, Tara)

提前谢谢！

推荐答案

以下是使用least和greatest的一个选项:

Here's one option using least and greatest:

select distinct least(a.name, b.name), greatest(a.name, b.name)
from table_a a 
  join table_b b on a.id = b.id_table_a

• SQL小提琴演示
• SQL Fiddle Demo

这篇关于从表联接中选择不同的有序对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！