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!

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