前几天发生在群里的讨论
下面有如下需求
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,2) =1
16  /RES
——
a PK b
b PK a
c PK a

解法3：递归查询
SQL> with dao as
2  (
select 1 c1,’a’ c2 from dual
3    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 cola||’ PK ‘||colb
10  from (
11  select prior c2 cola,c2 colb
12  from dao
13  connect by prior c1=c1+1)
14  where cola is not null
15  ;COLA||
——
b PK a
c PK b
b PK a

从实现方法上来看第一种是最好的方式，简单，高效。
我们写程序不一定非得要用某些复杂的特性，
只要能满足需求，越简单的，往往意味越高效。

顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶