2013年9月1日 星期日

以產品代碼第一碼為查詢條件找出符合之資料-Regular Express概述

本範例將查詢特定類型的產品資料,以產品代碼(Prod_No)第一碼為判斷依據,將列出第一碼為BCD等三類產品,SQL及執行結果如下:
SQL
結果
SELECT *
FROM
 (       
SELECT 'AC20' Prod_No
UNION ALL
SELECT 'BD20'
UNION ALL
SELECT 'CF20'
UNION ALL
SELECT 'DF40'
UNION ALL
SELECT 'EB20'
UNION ALL
SELECT 'FA30'
) Prod   
WHERE 1=1
 AND SUBSTRING(Prod_No, 1, 1) IN ('B', 'C', 'D')
ORDER BY 1

* 1. 以上資料只是譂述概念,實務上通常會在產品資料表(Prod)中產品代碼建立INDEX
    2. MSSQL語法為例,ORACLE請自行修改。

以上SQL利用SUBSTRING擷取出第一碼,並據以判斷是否符合BCD三者,查詢結果確實可符合所需,語法上非常具有可讀性,由於已應用函數於查詢欄位上,不論Prod_No是否已建立INDEX,均迫使資料庫以FULL TABLE SCAN方式進行,以下將列出SUBSTR(ING)LIKE+ORRegular Express3種方法,不過建議採行第2或第3種方法。

MSSQL
ORACLE
SELECT *
FROM
       (       
       SELECT 'AC20' Prod_No
       UNION ALL
       SELECT 'BD20'
       UNION ALL
       SELECT 'CF20'
       UNION ALL
       SELECT 'DF40'
       UNION ALL
       SELECT 'EB20'
       UNION ALL
       SELECT 'FA30'
    ) A   
WHERE 1=1
--方法1. SUSBSTRING
-- AND SUBSTRING(Prod_No, 1, 1)
--               IN ('B', 'C', 'D')
--方法2: LIKE + OR
/*AND (Prod_No LIKE 'B%'             
            OR Prod_No LIKE 'C%'
                 OR Prod_No LIKE 'D%'
                )*/
--方法3: LIKE [] (類似regular express語法)
       AND Prod_No LIKE '[BCD]%'       
ORDER BY 1
SELECT *
FROM
       (       
       SELECT 'AC20' Prod_No
     FROM DUAL
       UNION ALL
       SELECT 'BD20' FROM DUAL  
       UNION ALL
       SELECT 'CF20' FROM DUAL 
       UNION ALL
       SELECT 'DF40' FROM DUAL 
       UNION ALL
       SELECT 'EB20' FROM DUAL 
       UNION ALL
       SELECT 'FA30' FROM DUAL 
    ) A   
WHERE 1=1
--方法1: SUSBSTR
-- AND SUBSTR(Prod_No, 1, 1)
--             IN ('B', 'C', 'D')
--方法2: LIKE + OR
/*AND (Prod_No LIKE 'B%'                         
            OR Prod_No LIKE 'C%'
                 OR Prod_No LIKE 'D%'
                )*/
--方法3: REGEXP_LIKE
AND REGEXP_LIKE (Prod_No, '^[BCD]') 
ORDER BY 1
以上MSSQL/ORACLE兩種資料庫,所列之3種方法為對應之類似用法,將3種方法整理/說明如下表:

#
方法
語法
IX
說明
1
SUBSTR(ING)
SUBSTRING(Prod_No, 1, 1)              
     IN ('B', 'C', 'D')
N
INDEX失效,不建議使用。
2
LIKE + OR
Prod_No LIKE 'B%'            
OR Prod_No LIKE 'C%'
OR Prod_No LIKE 'D%'
Y
INDEX有效,若所查詢類別較少可用,但較多時因可讀性較差,則不建議使用。
3
Regular Express
S: Prod_No LIKE '[BCD]%'
O: REGEXP_LIKE (Prod_No, '^[BCD]')
Y
INDEX有效,語法上較少人使用,建議增加註解或教育訓練提昇技術能力。


本範例如何應用模糊比對進行篩選,以下簡述其概念用法,建議可自行至官方網站查閱相關文件。
MSSQL
是基於LIKE子句增加特徵(Pattern)比對運算,因此由萬用字元(%)置放位置即可決定字首、字尾或任何位置的模糊比對,可提供類似正則表示式(Regular Express)運算模式

ORACLE
10g起支援Regular Express的應用,本範例將使用REGEXP_LIKE函數,除使用特徵(Pattern)比對運算外,尚需增加位置指示字元如^對應輸入的字首、$』對應輸入的結尾,因『^』在特徵(Pattern)比對中代表『非』、『不存在』,請特別注意此兩者的差異。如下:


常見SQL (MSSQL )
MSSQL
ORACLE
身份
字號

SUBSTRING(ID_NO, 1, 1)
    between 'A' and 'Z'
身份字號第一碼為A-Z
ID_NO LIKE '[A-Z]%'
REGEXP_LIKE(ID_NO, '^[A-Za-z]')
員工
編號
SUBSTRING(EMP_NO, 1, 1) <>'0'
員工編號第1碼不為0
EMP_NO LIKE '[^0]%'
註: ^代表『非』
REGEXP_LIKE(EMP_NO, '^[^0]')
註: 1^代表字首, 2個為『非』

最後,此範例或有人會建議建立Functional Index以解決FULL TABLE SCAN的問題,就資源有限性及有效分配立場難以贊同,應多加思考如何在現有/簡單化之模式之下解決問題。

參考網站

沒有留言:

張貼留言