用PL/SQL寫還蠻簡單的，但是要挑戰用一句SQL達成真的是不太容易
Oracle DB 9.2.0.7
SELECT D.A
,D.B
,TRIM(REPLACE(D.D, ',', ' ')) E
FROM (SELECT C.A
,C.B
,SYS_CONNECT_BY_PATH(C.C, ',') D
FROM (SELECT A.A
,B.B
,TO_CHAR(A.A) || ' * ' || TO_CHAR(B.B) || ' = ' ||
LPAD(TO_CHAR(A.A * B.B), 2, ' ') C
,ROW_NUMBER() OVER(PARTITION BY A.A ORDER BY B.B) RN
FROM (SELECT LEVEL B
FROM DUAL
CONNECT BY LEVEL <= 9) B
,(SELECT LEVEL A
FROM DUAL
CONNECT BY LEVEL <= 9) A) C
START WITH C.RN = 1
CONNECT BY C.A = PRIOR C.A
AND C.RN - 1 = PRIOR C.RN) D
WHERE D.B = 9;
,D.B
,TRIM(REPLACE(D.D, ',', ' ')) E
FROM (SELECT C.A
,C.B
,SYS_CONNECT_BY_PATH(C.C, ',') D
FROM (SELECT A.A
,B.B
,TO_CHAR(A.A) || ' * ' || TO_CHAR(B.B) || ' = ' ||
LPAD(TO_CHAR(A.A * B.B), 2, ' ') C
,ROW_NUMBER() OVER(PARTITION BY A.A ORDER BY B.B) RN
FROM (SELECT LEVEL B
FROM DUAL
CONNECT BY LEVEL <= 9) B
,(SELECT LEVEL A
FROM DUAL
CONNECT BY LEVEL <= 9) A) C
START WITH C.RN = 1
CONNECT BY C.A = PRIOR C.A
AND C.RN - 1 = PRIOR C.RN) D
WHERE D.B = 9;
文章標籤
全站熱搜
Oracle (6)