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!
********************************************************************************************************************************************