用lead分析函數構造類似9*9乘法口訣的功能

今天又個兄弟求助，資料庫裡一個表有資料如下：
no  name
1    a
2    b
3    c
4    d
如何用一個sql顯示如下結果：
ab
ac
ad
bc
bd

cd

對於這種構造資料，是分析函數的強項，下面來做個實驗：

create table t (no number,name varchar(2));
insert into t values(1,'a');
insert into t values(2,'b');
insert into t values(3,'c');
insert into t values(4,'d');
commit;

實現1：
select decode(h2, '', '', h1 || h2) b,
       decode(h3, '', '', h1 || h3) c,
       decode(h4, '', '', h1 || h4) d
  from (select name h1,
               lead(name, 1) over(order by name) h2,
               lead(name, 2) over(order by name) h3,
               lead(name, 3) over(order by name) h4
          from t) ;

B    C    D
---- ---- ----
ab   ac   ad
bc   bd
cd

實現2：相對實現1對於行進行了轉換
with tt as(
select name h1,
               lead(name, 1) over(order by name) h2,
               lead(name, 2) over(order by name) h3,
               lead(name, 3) over(order by name) h4
          from t
)
select * from (select decode(h2, '', '', h1 || h2) b from tt
union
select decode(h3, '', '', h1 || h3) c from tt
union
select decode(h4, '', '', h1 || h4) d from tt)
where b is not null;

B
----
ab
ac
ad
bc
bd
cd

實現3：也可以不用分析函數

select a.name || b.name from t a, t b where a.no < b.no;

附錄，一句SQL實現9*9乘法口訣：

select r1 || '*' || r1 || '=' || r1 * r1 A,
decode(r2, '', '', r2 || '*' || r1 || '=' || r2 * r1) b,
decode(r3, '', '', r3 || '*' || r1 || '=' || r3 * r1) C,
decode(r4, '', '', r4 || '*' || r1 || '=' || r4 * r1) D,
decode(r5, '', '', r5 || '*' || r1 || '=' || r5 * r1) E,
decode(r6, '', '', r6 || '*' || r1 || '=' || r6 * r1) F,
decode(r7, '', '', r7 || '*' || r1 || '=' || r7 * r1) G,
decode(r8, '', '', r8 || '*' || r1 || '=' || r8 * r1) H,
decode(r9, '', '', r9 || '*' || r1 || '=' || r9 * r1) I
from (select level r1,
lag(level, 1) over(order by level) r2,
lag(level, 2) over(order by level) r3,
lag(level, 3) over(order by level) r4,
lag(level, 4) over(order by level) r5,
lag(level, 5) over(order by level) r6,
lag(level, 6) over(order by level) r7,
lag(level, 7) over(order by level) r8,
lag(level, 8) over(order by level) r9
from dual
connect by level < 10);