mysql - 无论列顺序如何，都显示不同的元组

Question

假设我有以下结果

----------------------
|   col1  |   col2   |
----------------------
|    a    |    b     |
|    b    |    a     |
|    c    |    d     |
|    e    |    f     |
----------------------

无论列顺序如何，我都想获得不同的元组。换句话说，(a, b) 和 (b, a) 被认为是“相同的”，因为改变顺序会使一个与另一个 (a, b) == (a, b) 相同。所以，执行查询后应该是:

----------------------
|   col1  |   col2   |
----------------------
|    a    |    b     | // or (b, a)
|    c    |    d     |
|    e    |    f     |
----------------------

任何查询专家都可以帮助我解决这个问题吗？我被困了几个小时，无法解决这个问题。

下面是我正在处理的详细场景。

我有以下关系:

Ships(name, country) // ("Lincoln", "USA") = "Ship Lincoln belongs to USA"
Battles(ship, battleName) // ("Lincoln", "WW2") = "Ship Lincoln fought in WW2"

我需要找到:列出所有在战斗中相互交战的国家对

我能够通过执行以下查询找到所有对:

 SELECT DISTINCT c1, c2
 FROM
 (SELECT DISTINCT s1.country as c1, battleName as b1
  FROM Ships as s1, Battles
  WHERE s1.name = ship) as t1
 JOIN
 (SELECT DISTINCT s2.country as c2, battleName as b2
  FROM Ships as s2, Battles
  WHERE s2.name = ship) as t2
 ON (b1 = b2)
 WHERE c1 <> c2

执行上述查询的结果是:

---------------------------------
|       c1      |       c2      |
---------------------------------
|       USA     |     Japan     |   // Row_1
|      Japan    |      USA      |   // Row_2
|     Germany   | Great Britain |   // Row_3
| Great Britain |    Germany    |   // Row_4
---------------------------------

但 Row_1 和 Row_2 与 Row_3 和 Row_4 相同。

我需要打印 Row_1 或 Row_2 以及 Row_3 或 Row_4 之一。

谢谢

Answer 1

这样试试

SELECT DISTINCT
       LEAST(s1.country, s2.country) c1,
       GREATEST(s1.country, s2.country) c2
  FROM battles b1 JOIN battles b2
    ON b1.battlename = b2.battlename
   AND b1.ship <> b2.ship JOIN ships s1
    ON b1.ship = s1.name JOIN ships s2
    ON b2.ship = s2.name
HAVING c1 <> c2

输出:

|      C1 |            C2 ||---------|---------------|| Germany | Great Britain ||   Japan |           USA |

这是 SQLFiddle 演示