用SQL生成对战表

前几天发生在群里的讨论
下面有如下需求
c1 球队ID
c2 球队名称
SQL> with dao as
2  (
3    select 1 c1,’a’ c2 from dual
4    union all
5     select 2 c1,’b’ c2 from dual
6     union all
7      select 3 c1,’c’ c2 from dual
8  )
9  select *
10  from dao
11  /C1 C
———- -
1 a
2 b
3 c

现在需要生成一张对战表
每个球队都要与其他球队对战一次。
解法1：简单不等连接

SQL> with dao as
2  (
3    select 1 c1,’a’ c2 from dual
4    union all
5     select 2 c1,’b’ c2 from dual
6     union all
7      select 3 c1,’c’ c2 from dual
8  )
9  select t1.c2||’ PK ‘||t2.c2
10  from dao t1,dao t2
11  where t1.c1 <t2.c1
12  /

T1.C2|
——
a PK b
a PK c
b PK c
解法2：分析函数
SQL> with dao as
2  (
3    select 1 c1,’a’ c2 from dual
4    union all
5     select 2 c1,’b’ c2 from dual
6     union all
7      select 3 c1,’c’ c2 from dual
8  )
9  select res
10  from
11  (
12  select row_number() over ( order by t1.c1+t1.c1) rn ,t1.c2||’ PK ‘||t2.c2 res
13  from dao t1,dao t2
14  where t1.c2 !=t2.c2 ) inner
15  where mod(inner.rn,