来自两个表的所有记录对oracle SQL

Question

我需要找到两个表之间的所有唯一记录对(而不是排列)。下面给出了A、A_1的所有排列

with a1
as(
select
'abc' as a,
1 as dummy
from dual

union all

select
'efg' as a
1 as dummy
from dual)

select
a1_1.a,
a1_2.a
from a1 a1_1
join a1 a1_2 on a1_1.dummy = a1_2.dummy
where a1_1.a != a1_2.a;

输出(带有上面的代码片段)：

+-----+-----+
|  A  | A_1 |
+-----+-----+
| efg | abc |
| abc | efg |
+-----+-----+

所需输出：

+-----+-----+
|  A  | A_1 |
+-----+-----+
| efg | abc |
+-----+-----+

有没有办法获得配对(而不是上面的排列)？

Answer 1

我想你只是想要<

select a1_1.a, a1_2.a
from a1 a1_1 join
     a1 a1_2
     on a1_1.dummy = a1_2.dummy and
        a1_1.a < a1_2.a;