显示不同的元组，无论列顺序如何 [英] Show distinct tuples regardless of column order

问题描述

说我有以下结果

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

无论列顺序如何，我都希望获得不同的元组.换句话说，将(a，b)和(b，a)视为相同"，因为更改顺序使另一个(a，b)==(a，b)相同.因此，执行查询后应该是:

I would like to get distinct tuple regardless of column order. In other words, (a, b) and (b, a) are considered "same" because changing the order make one same as the other (a, b) == (a, b). So, after executing query should be:

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

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

Can any query expert help me on this? I've been stuck for few hours and wasn't able to solve this.

下面是我正在研究的详细方案.

Below is my detailed scenario I'm working on.

我有以下关系:

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

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

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

I was able to find all pairs by executing below query:

 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

执行上述查询的结果是:

And the result of executing above query is:

---------------------------------
|       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相同.

But Row_1 and Row_2 are same as well as Row_3 and Row_4.

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

What I need is to print either one of Row_1 or Row_2 and either Row_3 or Row_4.

谢谢

推荐答案

以这种方式尝试

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 演示

Here is SQLFiddle demo

这篇关于显示不同的元组，无论列顺序如何的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！