2018年2月21日 星期三

九九乘法表-內建數值序列(Tally)功能之使用

九九乘法表是程式設計課程中常見迴圈撰寫之案例, 在資料庫應用上則常以T-SQLPL/SQLCURSOR方式完成,其實可用資料庫內建數值序列功能可同樣可以達成。

本範例重點在於如何應用數值序列以產生笛卡兒積(Cartesian product),許多原本認為無法(或難以)使用一組SQL之問題均可迎刃而解,如整個月完整銷售報告,可透過此方法創造(填補)出額外資料空間,即可用SQL取代原先CURSOR或程式迴圈作法。

SQL
MSSQL
WITH Num
AS
(
SELECT number No
FROM master.dbo.spt_values
WHERE 1=1
    AND type ='P'
    AND number BETWEEN 1 AND 9
)
SELECT  CAST(X.No AS VARCHAR)
      + ' * ' + CAST(Y.No AS VARCHAR) + ' = ' +
      + RIGHT(' ' + CAST(X.No * Y.No AS VARCHAR), 2) Result
FROM Num Y, Num X
ORACLE

WITH Num
AS
(
SELECT LEVEL No
FROM DUAL
CONNECT BY LEVEL<=9
)
SELECT X.No || ' * ' || Y.No || ' = ' || LPAD(X.No * Y.No, 2, ' ') Result
FROM Num X, Num Y

MSSQL可直接使用內建資料表master.dbo.spt_valuesORACLE並無對應系統資料表,但可應用遞迴展開CONNECT BY指令即可,兩者執行結果相同,如下圖所示。但請注意兩種資料庫SQL之順序不同,可推論二者在解析演算法時之差異。

沒有留言:

張貼留言