ORACLE SQl——9*9 乘法表的实现方法
来源:互联网 发布:修改ip软件 编辑:程序博客网 时间:2024/06/05 14:33
1、99乘法表方法一
select
max(case when aa < 1 then '' else '1*'||aa||'='||aa*1 end) a,
max(case when aa < 2 then ''else '2*'||aa||'='||aa*2end) b,
max(case when aa < 3 then ''else '3*'||aa||'='||aa*3end) c,
max(case when aa < 4 then ''else '4*'||aa||'='||aa*4end) d,
max(case when aa < 5 then ''else '5*'||aa||'='||aa*5end) e,
max(case when aa < 6 then ''else '6*'||aa||'='||aa*6end) f,
max(case when aa < 7 then ''else '7*'||aa||'='||aa*7end) g,
max(case when aa < 8 then ''else '8*'||aa||'='||aa*8end) h,
max(case when aa < 9 then ''else '9*'||aa||'='||aa*9end) i
from (
select 1 as aa from dual
union all
select 2 as aa from dual
union all
select 3 as aa from dual
union all
select 4 as aa from dual
union all
select 5 as aa from dual
union all
select 6 as aa from dual
union all
select 7 as aa from dual
union all
select 8 as aa from dual
union all
select 9 as aa from dual
--SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 10 (或者这一句)
--select rownum aa from all_objects where rownum <= 9 (或者这一句)
)
group by aa
order by 1
2、99乘法表方法二-
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 );
--order by 1 desc
3、99乘法表方法三
select rn,ltrim(max(sys_connect_by_path(product, ' , ')), ' , ') product
from (select rn, product,
min(product) over(partition by rn) product_min,
(row_number() over(order by rn, product)) + (dense_rank() over(order by rn)) numId
from
(select b.rn, a.rn || '*' || b.rn || '=' || a.rn * b.rn product
from
(select rownum rn from all_objects where rownum <= 9) a,
(select rownum rn from all_objects where rownum <= 9) b
where a.rn <= b.rn order by b.rn, product ) )
start with product = product_min
connect by numId - 1 = prior numId
group by rn
ORDER BY product;
4、99乘法表方法四
select ltrim(sys_connect_by_path
(rownum || '*' || lv || '=' || rpad(rownum * lv, 2),' '))
from (select level lv from dualconnect by level < 10)
where lv = 1
connect by lv + 1 = prior lv ;
5、99乘法表方法五
SELECT REVERSE(LTRIM((SYS_CONNECT_BY_PATH(REVERSE(ROWNUM || '*' || LV || '=' ||
LPAD(ROWNUM * LV, 2, '0')),' ')))) "乘法口诀"
FROM (SELECT LEVEL LV FROM DUALCONNECT BY LEVEL < 10)
WHERE LV = 1
CONNECT BY PRIOR LV = LV + 1;
6、99乘法表方法六
WITH x AS (SELECT level nFROM dualconnect by level < 10)
SELECT max(decode(a, 1, cnt)) as a,
max(decode(a, 2, cnt)) as b,
max(decode(a, 3, cnt)) as c,
max(decode(a, 4, cnt)) as d,
max(decode(a, 5, cnt)) as e,
max(decode(a, 6, cnt)) as f,
max(decode(a, 7, cnt)) as g,
max(decode(a, 8, cnt)) as h,
max(decode(a, 9, cnt)) as i
FROM ( SELECT c0.n a, c1.n b, c0.n || '*' ||c1.n || '=' || c0.n*c1.n cnt
FROM x c0, x c1 WHERE c0.n <= c1.n )
group by b
order by 1
7、99乘法表方法七
select ltrim(sys_connect_by_path(rownum - rn1+1||'*'||rownum || '=' || rpad(rownum * (rownum - rn1+1), 2) ,' '))
from (select rownum rn1 from dualconnect by rownum <=9)
where rn1 = 1
connect by rn1+1 = prior rn1;
--connect by prior rn1= rn1+1;
8、99乘法表方法八
select max(decode(rowrn, 1, vresult, null)) A,
max(decode(rowrn, 2, vresult, null)) B,
max(decode(rowrn, 3, vresult, null)) C,
max(decode(rowrn, 4, vresult, null)) D,
max(decode(rowrn, 5, vresult, null)) E,
max(decode(rowrn, 6, vresult, null)) F,
max(decode(rowrn, 7, vresult, null)) G,
max(decode(rowrn, 8, vresult, null)) H,
max(decode(rowrn, 9, vresult, null)) J
from
(select rn,row_number() over(partition by rn order by vresult) rowrn,vresult
from (select b.rn rn,a.rn || '*' || b.rn || ' = ' || a.rn * b.rn vresult
from (select rownum rn from dualconnect by rownum <= 9) a,
(select rownum rn from dual connect by rownum <= 9) b
where a.rn <= b.rn)) group by rn;
********************************************************************************************************************************************
==== Till good is better, but better best
====“我的努力不会停止,敬请期待吧!”My trying hard will go on!Please wait and see!
********************************************************************************************************************************************
- ORACLE SQl——9*9 乘法表的实现方法
- 用SQL实现9*9乘法表
- sql输出9*9乘法口诀表
- 一条SQL生成9*9乘法表
- Java实现各式各样的9*9乘法表
- SQL SERVER实现乘法口诀表
- 简单实现9*9乘法表
- java实现9*9乘法表
- C语言实现9-9乘法表
- PHP实现9x9乘法表
- java--方法调用实现乘法表
- Java程序9 X 9乘法表(补:第二种方法:递归算法实现99乘法表|再补:解析递归算法)
- 【SQL】Oracle实现小写金额转换成大写的方法
- 源码-印度式乘法口诀表(“双十九”)的PL/SQL实现
- Oracle 9 中PL/SQL实现FTP功能的文件传输
- Matlab Tricks(六)—— 矩阵乘法的实现
- Python写9*9乘法表的两种简单方法
- oracle plsql 乘法表
- linux查找文件命令find
- 网页素材大宝库:25套高质量的网站纹理背景图片素材
- 大数据量时,Gridview无法导出Excel
- Struts2入门配置小实例
- 发送邮件,支持群发,非常好用
- ORACLE SQl——9*9 乘法表的实现方法
- mjpg-streamer源码分析
- 分享35套很不错的抽象纹理背景素材
- 用javascript实现有效时间的控制,并显示要过期的时间
- iOS数据、界面分开设计模式遇到的一个问题
- 一台电脑上面安装多个Tomcat 服务
- 硬盘终极优化
- Linux 系统内核的调试
- 该死的“代码就是文档”