--实现1:
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
); --实现2:
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; --实现3:
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; --实现4:
select reverse(ltrim((sys_connect_by_path(reverse(rownum || 'x' || lv || '=' ||
lpad(rownum * lv, 2, '')),
' ')))) "乘法口诀"
from (select level lv from dual connect by level < 10)
where lv = 1
connect by prior lv = lv + 1; --实现5:
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; --实现6:
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; --实现7:
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; --方法8:
select a.rn, substr(max( sys_connect_by_path(
case when a.rn*b.rn >= 10 then substr(translate(b.rn
||'*'
||a.rn
||'='
||a.rn*b.rn,'1234567890*=','一二三四五六七八九十'),1,
case when mod(a.rn*b.rn,10) = 0 or a.rn*b.rn > 20 then 3 else 2 end)
||'十'
|| translate(mod(a.rn*b.rn,10),'','一二三四五六七八九')
else translate(b.rn
||'*'
||a.rn
||'='
||a.rn*b.rn,'123456789=*','一二三四五六七八九得')
end ,',')),2) 口诀
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
connect by prior a.rn = a.rn
and prior b.rn = b.rn-1
start with b.rn = 1
group by a.rn
order by 1; --方法10:
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; --方法11:
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;
/ --方法12:
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 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
) --方法13:
select max(case when a < 1 then '' else '1*'+cast(a as varchar)+'='+cast(a*1 as varchar) end) as [],
max(case when a < 2 then '' else '2*'+cast(a as varchar)+'='+cast(a*2 as varchar) end) as [],
max(case when a < 3 then '' else '3*'+cast(a as varchar)+'='+cast(a*3 as varchar) end) as [],
max(case when a < 4 then '' else '4*'+cast(a as varchar)+'='+cast(a*4 as varchar) end) as [],
max(case when a < 5 then '' else '5*'+cast(a as varchar)+'='+cast(a*5 as varchar) end) as [],
max(case when a < 6 then '' else '6*'+cast(a as varchar)+'='+cast(a*6 as varchar) end) as [],
max(case when a < 7 then '' else '7*'+cast(a as varchar)+'='+cast(a*7 as varchar) end) as [],
max(case when a < 8 then '' else '8*'+cast(a as varchar)+'='+cast(a*8 as varchar) end) as [],
max(case when a < 9 then '' else '9*'+cast(a as varchar)+'='+cast(a*9 as varchar) end) as []
from (select rownum as a from dual connect by rownum <= 9)