oracle 乘法表,ORACLE SQl——9*9 乘法表的实现方法

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*2 end) b,

max(case  when  aa < 3 then ''else  '3*'||aa||'='||aa*3 end) c,

max(case  when  aa < 4 then ''else  '4*'||aa||'='||aa*4 end) d,

max(case  when  aa < 5 then ''else  '5*'||aa||'='||aa*5 end) e,

max(case  when  aa < 6 then ''else  '6*'||aa||'='||aa*6 end) f,

max(case  when  aa < 7 then ''else  '7*'||aa||'='||aa*7 end) g,

max(case  when  aa < 8 then ''else  '8*'||aa||'='||aa*8 end) h,

max(case  when  aa < 9 then ''else  '9*'||aa||'='||aa*9 end) 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 dual connect 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!

********************************************************************************************************************************************