打印九九乘法表
?
?
??? 網(wǎng)上找到的打印九九乘法表的SQL程序，關(guān)鍵的一些思路還是很有借鑒價值的。其實幾乎所有比較復(fù)雜的SQL基本上都會用到像lag、connect by、rownum、分析函數(shù)這些東西，逐漸看著看著，也就習(xí)慣了。下面分幾個類型介紹：
?
一、枚舉法：
?
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);
?
1、先用connect by列出1-9的數(shù)列
2、用lag函數(shù)排好次序
3、最后打印出來。
?
?
二、高級枚舉法：
?
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)) H,
?????? max(decode(a, 9, cnt)) I
? from (select a.rn a, b.rn b, a.rn || '*' || b.rn || '=' || a.rn * b.rn cnt
????????? 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 b
order by 1;
?
1、列出1-9的數(shù)列
2、用笛卡爾積做出各個乘積
3、排除多于的值，并排序輸出
?
?
三、匯總結(jié)果法：
?
select max(substr(sys_connect_by_path(b.n || '*' || a.n || '=' || a.n * b.n,', '),3)) as val
? from (select rownum n from all_objects where rownum <= 9) a,
?????? (select rownum n from all_objects where rownum <= 9) 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
order by a.n;
?
1、前面和枚舉法一樣列出1-9的數(shù)列，然后用笛卡爾乘積
2、在打印時使用sys_connect_by_path函數(shù)，打印到一個字段中
3、上面的程序中間步驟比較省略，下面這個程序比較詳細一些
?
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 rn;
?
?
四、牛逼的簡化SQL：
?
SELECT REPLACE(REVERSE(sys_connect_by_path(REVERSE(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;
?
1、列出1-9序列
2、使用本身的循環(huán) lv + 1 = prior lv
3、使用sys_connect_by_path函數(shù)打印
4、具體的格式調(diào)整的方法還有很多，不列舉
?
?
總結(jié)：
?
1、要用一個字段打印一列是不太現(xiàn)實的，擴展性不大，要是99*99乘法表就不可能打印
2、sys_connect_by_path還是一個比較實用的函數(shù)。
?