用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)
9select *
10from 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)
9select t1.c2||’ PK ‘||t2.c2
10from dao t1,dao t2
11where 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)
9select res
10from
11(
12select row_number() over ( order by t1.c1+t1.c1) rn ,t1.c2||’ PK ‘||t2.c2 res
13from dao t1,dao t2
14where t1.c2 !=t2.c2 ) inner
15where 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)
9select cola||’ PK ‘||colb
10from (
11select prior c2 cola,c2 colb
12from dao
13connect by prior c1=c1+1)
14where cola is not null
15;COLA||
——
b PK a
c PK b
b PK a
从实现方法上来看第一种是最好的方式，简单，高效。
我们写程序不一定非得要用某些复杂的特性，
只要能满足需求，越简单的，往往意味越高效。
顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶
页:
[1]