打印九九乘法表
?
?
??? 網(wǎng)上找到的打印九九乘法表的SQL程序，關(guān)鍵的一些思路還是很有借鑒價(jià)值的。其實(shí)幾乎所有比較復(fù)雜的SQL基本上都會(huì)用到像lag、connect by、rownum、分析函數(shù)這些東西，逐漸看著看著，也就習(xí)慣了。下面分幾個(gè)類型介紹：
?
一、枚舉法：
?
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、最后打印出來。
?
?
二、高級(jí)枚舉法：
?
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、用笛卡爾積做出各個(gè)乘積
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、在打印時(shí)使用sys_connect_by_path函數(shù)，打印到一個(gè)字段中
3、上面的程序中間步驟比較省略，下面這個(gè)程序比較詳細(xì)一些
?
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、要用一個(gè)字段打印一列是不太現(xiàn)實(shí)的，擴(kuò)展性不大，要是99*99乘法表就不可能打印
2、sys_connect_by_path還是一個(gè)比較實(shí)用的函數(shù)。
?