單一 SQL 實現九九乘法表：Oracle 分析函數巧妙應用

核心思路

利用 Oracle CONNECT BY LEVEL 產生 1~9 序列，配合 LAG() 分析函數取得前 1~8 列的值，再以 DECODE 條件式輸出對應乘法算式，單一查詢即可產生完整九九乘法表矩陣。

完整 SQL

SELECT 
    r1 || '*' || r1 || '=' || r1 * r1 AS A,
    DECODE(r2, NULL, NULL, r2 || '*' || r1 || '=' || r2 * r1) AS B,
    DECODE(r3, NULL, NULL, r3 || '*' || r1 || '=' || r3 * r1) AS C,
    DECODE(r4, NULL, NULL, r4 || '*' || r1 || '=' || r4 * r1) AS D,
    DECODE(r5, NULL, NULL, r5 || '*' || r1 || '=' || r5 * r1) AS E,
    DECODE(r6, NULL, NULL, r6 || '*' || r1 || '=' || r6 * r1) AS F,
    DECODE(r7, NULL, NULL, r7 || '*' || r1 || '=' || r7 * r1) AS G,
    DECODE(r8, NULL, NULL, r8 || '*' || r1 || '=' || r8 * r1) AS H,
    DECODE(r9, NULL, NULL, r9 || '*' || r1 || '=' || r9 * r1) AS I
FROM (
    SELECT 
        LEVEL AS r1,
        LAG(LEVEL, 1) OVER (ORDER BY LEVEL) AS r2,
        LAG(LEVEL, 2) OVER (ORDER BY LEVEL) AS r3,
        LAG(LEVEL, 3) OVER (ORDER BY LEVEL) AS r4,
        LAG(LEVEL, 4) OVER (ORDER BY LEVEL) AS r5,
        LAG(LEVEL, 5) OVER (ORDER BY LEVEL) AS r6,
        LAG(LEVEL, 6) OVER (ORDER BY LEVEL) AS r7,
        LAG(LEVEL, 7) OVER (ORDER BY LEVEL) AS r8,
        LAG(LEVEL, 8) OVER (ORDER BY LEVEL) AS r9
    FROM DUAL
    CONNECT BY LEVEL < 10
);

執行結果示意

A           B           C           D           E           F           G           H           I
1*1=1       2*1=2       3*1=3       4*1=4       5*1=5       6*1=6       7*1=7       8*1=8       9*1=9
2*2=4       3*2=6       4*2=8       5*2=10      6*2=12      7*2=14      8*2=16      9*2=18      (null)
3*3=9       4*3=12      5*3=15      6*3=18      7*3=21      8*3=24      9*3=27      (null)      (null)
...
9*9=81      (null)      (null)      (null)      (null)      (null)      (null)      (null)      (null)

每列代表固定被乘數 (r1)，各欄位為乘數 1~9 (r1~r9)；下三角區域因乘數 > 被乘數而顯示 NULL (由 DECODE 控制)。

關鍵技術解析

跨資料庫移植提示

• PostgreSQL：GENERATE_SERIES(1,9) + LAG() + CASE WHEN。
• SQL Server：WITH nums AS (SELECT 1 n UNION ALL SELECT n+1 FROM nums WHERE n<9) + LAG() + CASE。
• MySQL 8.0+：WITH RECURSIVE nums AS (...) SELECT ... LAG() OVER ...。
• SQLite：遞迴 CTE + LAG() (3.25+ 支援窗口函數)。

延伸應用

同樣技巧可產生：

• 任意 N x N 乘法表 (調整 LEVEL < N+1 與 LAG 參數)。
• 帕斯卡三角形、費波納契數列等數學序列矩陣。
• 日曆矩陣 (年/月/日交叉表)。