oracle 实现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);
--- 实现二
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;
--- 实现三
select ltrim(sys_connect_by_path
(rownum || '*' || lv || '=' || rpad(rownum * lv, 2),' ')
)
from (select level lv from dual connect by level < 10)
where lv = 1
connect by lv + 1 = prior lv ;
---实现四
SELECT REVERSE(LTRIM((SYS_CONNECT_BY_PATH(REVERSE(ROWNUM || 'X' || LV || '=' ||
LPAD(ROWNUM * LV, 2, '0')),
' ')))) "乘法口诀"
FROM (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 10)
WHERE LV = 1
CONNECT BY PRIOR LV = LV + 1;--- 实现五
WITH x AS
(SELECT level n FROM 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 ;
--- 实现六
select ltrim(sys_connect_by_path
(rownum - rn1+1||'*'||rownum || '=' || rpad(rownum * (rownum - rn1+1), 2) ,' '))
from
(select rownum rn1 from dual connect by rownum <=9)
where rn1 = 1
connect by rn1+1 = prior rn1;
--- 实现七
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 dual connect by rownum <= 9) a,
(select rownum rn from dual connect by rownum <= 9) b
where a.rn <= b.rn))
group by rn;
--- 实现一
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);
--- 实现二
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;
--- 实现三
select ltrim(sys_connect_by_path
(rownum || '*' || lv || '=' || rpad(rownum * lv, 2),' ')
)
from (select level lv from dual connect by level < 10)
where lv = 1
connect by lv + 1 = prior lv ;
---实现四
SELECT REVERSE(LTRIM((SYS_CONNECT_BY_PATH(REVERSE(ROWNUM || 'X' || LV || '=' ||
LPAD(ROWNUM * LV, 2, '0')),
' ')))) "乘法口诀"
FROM (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 10)
WHERE LV = 1
CONNECT BY PRIOR LV = LV + 1;--- 实现五
WITH x AS
(SELECT level n FROM 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 ;
--- 实现六
select ltrim(sys_connect_by_path
(rownum - rn1+1||'*'||rownum || '=' || rpad(rownum * (rownum - rn1+1), 2) ,' '))
from
(select rownum rn1 from dual connect by rownum <=9)
where rn1 = 1
connect by rn1+1 = prior rn1;
--- 实现七
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 dual connect by rownum <= 9) a,
(select rownum rn from dual connect by rownum <= 9) b
where a.rn <= b.rn))
group by rn;
进步很大
赞个
实现2里面少写了个group by吧,执行不了
--方法一
declare
v_result varchar2(200);
begin
for i in 1..9 loop
select wmsys.wm_concat(rownum||'*'||i||'='||rownum*i) into v_result from dual connect by rownum<=i;
dbms_output.put_line(v_result);
end loop;
end;
--方法二
declare
i int;
j int;
begin
i:=1;
j:=1;
while i < 10
loop
while j <= i
loop
dbms_output.put(j||'*'||i||'=');
if length(i*j) = 1 and j!=1 then
dbms_output.put(' ');
end if;
dbms_output.put(i*j||' ');
j:=j+1;
end loop;
j:=1;
i:=i+1;
dbms_output.put_line(' ');
end loop;
end;
/ --方法三
declare
begin
for i in 1..9 loop
for j in 1 .. i loop
dbms_output.put(j||'*'||i||'=');
if length(i*j) = 1 and j!=1 then
dbms_output.put(' ');
end if;
dbms_output.put(i*j);
dbms_output.put(' ');
end loop;
dbms_output.put_line(' ');
end loop;
end;
/
select row_no,
max( decode( col_no, 1, value_no, '' ) ) 1,
max( decode( col_no, 2, value_no, '' ) ) 2,
max( decode( col_no, 3, value_no, '' ) ) 3,
max( decode( col_no, 4, value_no, '' ) ) 4,
max( decode( col_no, 5, value_no, '' ) ) 5,
max( decode( col_no, 6, value_no, '' ) ) 6,
max( decode( col_no, 7, value_no, '' ) ) 7,
max( decode( col_no, 8, value_no, '' ) ) 8,
max( decode( col_no, 9, value_no, '' ) ) 9
from
( select a.rn col_no, b.rn row_no, a.rn || '*' || b.rn || '=' || a.rn * b.rn value_no
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
)
group by row_no
order by row_no
;
max(case when a < 2 then '' else '2*'+cast(a as varchar)+'='+cast(a*2 as varchar) end) as [2],
max(case when a < 3 then '' else '3*'+cast(a as varchar)+'='+cast(a*3 as varchar) end) as [3],
max(case when a < 4 then '' else '4*'+cast(a as varchar)+'='+cast(a*4 as varchar) end) as [4],
max(case when a < 5 then '' else '5*'+cast(a as varchar)+'='+cast(a*5 as varchar) end) as [5],
max(case when a < 6 then '' else '6*'+cast(a as varchar)+'='+cast(a*6 as varchar) end) as [6],
max(case when a < 7 then '' else '7*'+cast(a as varchar)+'='+cast(a*7 as varchar) end) as [7],
max(case when a < 8 then '' else '8*'+cast(a as varchar)+'='+cast(a*8 as varchar) end) as [8],
max(case when a < 9 then '' else '9*'+cast(a as varchar)+'='+cast(a*9 as varchar) end) as [9]from (
select 1 as a
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as t1
group by a
SELECT DECODE(R1,NULL,NULL,R1 || '*' || ROWNUM ||'='|| R1* ROWNUM) A,
DECODE(R2,NULL,NULL,R2 || '*' || ROWNUM ||'='|| R2* ROWNUM) B,
DECODE(R3,NULL,NULL,R3 || '*' || ROWNUM ||'='|| R3* ROWNUM) C,
DECODE(R4,NULL,NULL,R4 || '*' || ROWNUM ||'='|| R4* ROWNUM) D,
DECODE(R5,NULL,NULL,R5 || '*' || ROWNUM ||'='|| R5* ROWNUM) E,
DECODE(R6,NULL,NULL,R6 || '*' || ROWNUM ||'='|| R6* ROWNUM) F,
DECODE(R7,NULL,NULL,R7 || '*' || ROWNUM ||'='|| R7* ROWNUM) G,
DECODE(R8,NULL,NULL,R8 || '*' || ROWNUM ||'='|| R8* ROWNUM) H,
DECODE(R9,NULL,NULL,R9 || '*' || ROWNUM ||'='|| R9* ROWNUM) I
FROM (
SELECT 1 R1,
DECODE(SIGN(LEVEL - 2), -1, NULL, 2) R2,
DECODE(SIGN(LEVEL - 3), -1, NULL, 3) R3,
DECODE(SIGN(LEVEL - 4), -1, NULL, 4) R4,
DECODE(SIGN(LEVEL - 5), -1, NULL, 5) R5,
DECODE(SIGN(LEVEL - 6), -1, NULL, 6) R6,
DECODE(SIGN(LEVEL - 7), -1, NULL, 7) R7,
DECODE(SIGN(LEVEL - 8), -1, NULL, 8) R8,
DECODE(SIGN(LEVEL - 9), -1, NULL, 9) R9
FROM DUAL
CONNECT BY LEVEL < 10)