用lead分析函数结构类似9*9乘法口诀的功能
日期：2014-05-16 浏览次数：20574 次
今天又个兄弟求助，数据库里一个表有数据如下：
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
