一句SQL实现输出九九乘法表

练练手，一句SQL实现输出九九乘法表

http://www.itpub.net/762215.html

[@more@]

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

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;

with t as (select level as n from dual connect by level <=9)
select max(substr(sys_connect_by_path(b.n || '*' || a.n || '=' || a.n * b.n, ', '),3)) as val
from t a, t b
where a.n >= b.n
start with b.n=1
connect by a.n=prior a.n and b.n=prior b.n+1
group by a.n

select replace(reverse(sys_connect_by_path(reverse(rownum || '*' || lv || '=' || rpad(rownum * lv, 2)),'/ ')),'/') aa,
replace(reverse(sys_connect_by_path(reverse(lv || '*' || rownum || '=' || rpad(rownum * lv, 2)),'/ ')),'/') bb
from (select level lv from dual connect by level < 10)
where lv = 1
connect by lv + 1 = prior lv;

select r1 || '*' || 1 || '=' || r1 * 1 A,
decode(r2, '', '', r2 || '*' || 2 || '=' || r2 * 2) b,
decode(r3, '', '', r3 || '*' || 3 || '=' || r3 * 3) C,
decode(r4, '', '', r4 || '*' || 4 || '=' || r4 * 4) D,
decode(r5, '', '', r5 || '*' || 5 || '=' || r5 * 5) E,
decode(r6, '', '', r6 || '*' || 6 || '=' || r6 * 6) F,
decode(r7, '', '', r7 || '*' || 7 || '=' || r7 * 7) G,
decode(r8, '', '', r8 || '*' || 8 || '=' || r8 * 8) H,
decode(r9, '', '', r9 || '*' || 9 || '=' || r9 * 9) I
from (select level r1,
lag(level+1, 1) over(order by level) r2,
lag(level+2, 2) over(order by level) r3,
lag(level+3, 3) over(order by level) r4,
lag(level+4, 4) over(order by level) r5,
lag(level+5, 5) over(order by level) r6,
lag(level+6, 6) over(order by level) r7,
lag(level+7, 7) over(order by level) r8,
lag(level+8, 8) over(order by level) r9
from dual
connect by level < 10)

WITH x AS
(SELECT level n FROM dual connect by level < 10)
SELECT
max(decode(a, 1, cnt)) a,
max(decode(a, 2, cnt)) b,
max(decode(a, 3, cnt)) c,
max(decode(a, 4, cnt)) d,
max(decode(a, 5, cnt)) e,
max(decode(a, 6, cnt)) f,
max(decode(a, 7, cnt)) g,
max(decode(a, 8, cnt)) g,
max(decode(a, 9, cnt)) 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 max(decode(rowrn, 1, vresult, null)),
max(decode(rowrn, 2, vresult, null)),
max(decode(rowrn, 3, vresult, null)),
max(decode(rowrn, 4, vresult, null)),
max(decode(rowrn, 5, vresult, null)),
max(decode(rowrn, 6, vresult, null)),
max(decode(rowrn, 7, vresult, null)),
max(decode(rowrn, 8, vresult, null)),
max(decode(rowrn, 9, vresult, null))
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 LTRIM(MAX(SYS_CONNECT_BY_PATH(FORMULA, ' ')), ' ') AS FORMULA
FROM (
SELECT A.RNUM AS ARNUM, B.RNUM AS BRNUM,
A.RNUM || '*' || B.RNUM || '=' || A.RNUM * B.RNUM AS FORMULA,
A.RNUM - 1 AS PREV
FROM (SELECT ROWNUM RNUM FROM ALL_OBJECTS WHERE ROWNUM < 10) A
INNER JOIN
(SELECT ROWNUM RNUM FROM ALL_OBJECTS WHERE ROWNUM < 10) B
ON B.RNUM >= A.RNUM
)
START WITH ARNUM = 1
CONNECT BY PREV = PRIOR ARNUM AND BRNUM = PRIOR BRNUM
GROUP BY BRNUM

Select ltrim(max(sys_connect_by_path(cj, ',')), ',') aa From (
Select r p,Row_number() over (Partition By r Order By cj) c ,cj From
(Select r,n||'*'||r||'='||r*n cj From
(Select Rownum r From dual Connect By Rownum<10) a,
(Select Rownum n From dual Connect By Rownum<10) b
Where r>=n Order By r))
Start With c=1
Connect By c-1 = Prior c And p = Prior p
Group By p

select decode(sign(a1 - c), 1, null, a1||'*'||c||'='||a1*c) "1",
decode(sign(a2 - c), 1, null, a2||'*'||c||'='||a2*c) "2",
decode(sign(a3 - c), 1, null, a3||'*'||c||'='||a3*c) "3",
decode(sign(a4 - c), 1, null, a4||'*'||c||'='||a4*c) "4",
decode(sign(a5 - c), 1, null, a5||'*'||c||'='||a5*c) "5",
decode(sign(a6 - c), 1, null, a6||'*'||c||'='||a6*c) "6",
decode(sign(a7 - c), 1, null, a7||'*'||c||'='||a7*c) "7",
decode(sign(a8 - c), 1, null, a8||'*'||c||'='||a8*c) "8",
decode(sign(a9 - c), 1, null, a9||'*'||c||'='||a9*c) "9"
from (select 1 a1, 2 a2, 3 a3, 4 a4, 5 a5, 6 a6, 7 a7, 8 a8, 9 a9 from dual) x,
(select 1 c from dual
union
select 2 c from dual
union
select 3 c from dual
union
select 4 c from dual
union
select 5 c from dual
union
select 6 c from dual
union
select 7 c from dual
union
select 8 c from dual
union
select 9 c from dual) y

SELECT x.a1 ||'*'|| y.lv ||'='|| x.a1*y.lv A
,x.a2 ||'*'|| y.lv ||'='|| x.a2*y.lv B
,x.a3 ||'*'|| y.lv ||'='|| x.a3*y.lv C
,x.a4 ||'*'|| y.lv ||'='|| x.a4*y.lv D
,x.a5 ||'*'|| y.lv ||'='|| x.a5*y.lv E
,x.a6 ||'*'|| y.lv ||'='|| x.a6*y.lv F
,x.a7 ||'*'|| y.lv ||'='|| x.a7*y.lv G
,x.a8 ||'*'|| y.lv ||'='|| x.a8*y.lv H
,x.a9 ||'*'|| y.lv ||'='|| x.a9*y.lv I
FROM (SELECT 1 a1, 2 a2 ,3 a3 ,4 a4 ,5 a5 ,6 a6 ,7 a7 ,8 a8 ,9 a9
FROM dual) x
,(SELECT LEVEL lv
FROM dual
CONNECT BY LEVEL < 10) y

--[CODE]--?建一?函?change,用??十?制??化成?中文?字
create or replace function change(v_digital varchar2)
return varchar2 is
result varchar2(10);
begin
result := v_digital;
if result >10 then
select decode(substr(result,1,1),'1','十',
'2','二十',
'3','三十',
'4','四十',
'5','五十',
'6','六十',
'7','七十',
'8','八十',
'9','九十')
||decode(substr(result,2,1),'1','一',
'2','二',
'3','三',
'4','四',
'5','五',
'6','六',
'7','七',
'8','八',
'9','九')
into result from dual;
else
result := replace(result,'10','一十');
result := replace(result,'1','一');
result := replace(result,'2','二');
result := replace(result,'3','三');
result := replace(result,'4','四');
result := replace(result,'5','五');
result := replace(result,'6','六');
result := replace(result,'7','七');
result := replace(result,'8','八');
result := replace(result,'9','九');
end if;
return result;
end change;

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

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92289/viewspace-968955/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92289/viewspace-968955/