用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’);

今天又个兄弟求助，数据库里一个表有数据如下：
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本文来源gao.dai.ma.com搞@代*码#网, 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);

喜欢 (0)赏

[搞代码]

分享 (0)