用SQL实现99乘法表

上一篇 / 下一篇 2008-01-04 17:10:02 / 个人分类：ORACLE

今天和同事聊天，同事提到了用SQL实现99乘法表。当时我觉得应该很简单，于是尝试了一下，结果这么简单的SQL居然用了10分钟才搞定。

SQL实现如下：

SQL> COL A FORMAT A6ITPUB个人空间DqC*Yo b|Kt
SQL> COL B FORMAT A6
B9bBj5p5?:Iz.o0SQL> COL C FORMAT A6
wRb Z,R![ }'V6o0SQL> COL D FORMAT A6
9d$s`/QgUa0SQL> COL E FORMAT A6
Z)n.paF OC0SQL> COL F FORMAT A6ITPUB个人空间+qNf ` X8A
SQL> COL G FORMAT A6ITPUB个人空间x+Ag{U`:s
SQL> COL H FORMAT A6
U7}'h"j$`2ltVL6Qib*U0SQL> COL I FORMAT A6
aN1e!ceZc0SQL> WITHITPUB个人空间 Z[RVmh9}qOxV
2 NUMS AS (SELECT ROWNUM NUM FROM DUAL CONNECT BY LEVEL < 10)
qV}BVrR0 3 SELECTITPUB个人空间Tk*Y#A|[o+y.h {`
4     MAX(DECODE(A, 1, EXP)) A,ITPUB个人空间}4ton{eU O
5     MAX(DECODE(A, 2, EXP)) B,ITPUB个人空间O8I8n!I@N
6     MAX(DECODE(A, 3, EXP)) C,
)~g%z%Hy?/A R0 7     MAX(DECODE(A, 4, EXP)) D,ITPUB个人空间7S,s-syZ XUF
8     MAX(DECODE(A, 5, EXP)) E,ITPUB个人空间thTQ+_,sx
9     MAX(DECODE(A, 6, EXP)) F,
c9H7|By!Hs4^C+[0 10     MAX(DECODE(A, 7, EXP)) G,
;QQn_:p3HA1[)Wn:E0 11     MAX(DECODE(A, 8, EXP)) H,
UV?8tj7X{8_[0 12     MAX(DECODE(A, 9, EXP)) I
_ s`Y(L3b6Q0 13 FROM
ym8ke[0a0 14 (
-h*mf&i$ZV#OBu0 15     SELECT
3c/R!Ets{ C$N(A/v^0 16             A.NUM A,ITPUB个人空间,\NJE$O h` e
17             B.NUM B,
me1}*nul0 18             A.NUM || '*' || B.NUM || '=' || A.NUM * B.NUM EXPITPUB个人空间[:xi)C#X M7P mE
19     FROM
ZX-AW;zC8T0 20     NUMS A,
G,\9L.pFt0 21     NUMS B
C,\(\#t!W0 22     WHERE A.NUM <= B.NUMITPUB个人空间$|MadLb/xA6m r
23 )ITPUB个人空间`wA9B$pNE;j;Td
24 GROUP BY B;

A B C D E F G H IITPUB个人空间7_[2Fd1~K9|
------ ------ ------ ------ ------ ------ ------ ------ ------ITPUB个人空间av ?}T9e*G"W0g
1*1=1
-Z^7p4Q2N6xDMD01*2=2 2*2=4ITPUB个人空间%|:s#T-@ ]7dX
1*3=3 2*3=6 3*3=9ITPUB个人空间]:Q?6i!\oV
1*4=4 2*4=8 3*4=12 4*4=16ITPUB个人空间1qOc8p.U2I `1D-whg
1*5=5 2*5=10 3*5=15 4*5=20 5*5=25ITPUB个人空间+J H?:]|+w
1*6=6 2*6=12 3*6=18 4*6=24 5*6=30 6*6=36ITPUB个人空间7s| _f B#`PpBx
1*7=7 2*7=14 3*7=21 4*7=28 5*7=35 6*7=42 7*7=49ITPUB个人空间mp|7~-? qiCg
1*8=8 2*8=16 3*8=24 4*8=32 5*8=40 6*8=48 7*8=56 8*8=64
e)\0c's U6WyH)]01*9=9 2*9=18 3*9=27 4*9=36 5*9=45 6*9=54 7*9=63 8*9=72 9*9=81

9 rows selected.

这个SQL就是通过一个构造和一个标准行列转换组成，就不多解释了。

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

引用删除 yangtingkun / 2008-01-23 15:16:01: 方法2很强。
应该是nyfor给出的。

引用删除松松 / 2008-01-23 15:00:10: 方法2：
select reverse(sys_connect_by_path
(rpad(reverse(to_char(rownum * lv)), 2) || '=' || rownum || '*' || lv ,' '))
from (select level lv from dual connect by level < 10)
where lv = 1
connect by lv + 1 = prior lv;
--这一个是从一位高手那里摘的，觉得很不错

引用删除松松 / 2008-01-23 14:38:54: 方法1：
select max(decode(X,1,S)) mul_1,max(decode(X,2,S)) mul_2,max(decode(X,3,S)) mul_3,max(decode(X,4,S)) mul_4,max(decode(X,5,S)) mul_5,max(decode(X,6,S)) mul_6,max(decode(X,7,S)) mul_7,max(decode(X,8,S)) mul_8,max(decode(X,9,S)) mul_9
  from
  (select X,Y,X || '*' || Y || '=' || X * Y S
   from
   (select level X from DUAL connect by level < 10) A,
   (select level Y from DUAL connect by level < 10) B
where A.X <= B.Y)
group by Y;

查看全部评论