打印九九乘法表
网上找到的打印九九乘法表的SQL程序，关键的一些思路还是很有借鉴价值的。其实几乎所有比较复杂的SQL基本上都会用到像lag、connect by、rownum、分析函数这些东西，逐渐看着看着，也就习惯了。下面分几个类型介绍：
一、枚举法：
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的数列
2、用lag函数排好次序
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的数列
2、用笛卡尔积做出各个乘积
3、排除多于的值，并排序输出
三、汇总结果法：
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的数列，然后用笛卡尔乘积
2、在打印时使用sys_connect_by_path函数，打印到一个字段中
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、使用本身的循环 lv + 1 = prior lv
3、使用sys_connect_by_path函数打印
4、具体的格式调整的方法还有很多，不列举
总结：
1、要用一个字段打印一列是不太现实的，扩展性不大，要是99*99乘法表就不可能打印
2、sys_connect_by_path还是一个比较实用的函数。