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擷取出第一碼，並據以判斷是否符合B、C及D三者，查詢結果確實可符合所需，語法上非常具有可讀性，由於已應用函數於查詢欄位上，不論Prod_No是否已建立INDEX，均迫使資料庫以FULL TABLE SCAN方式進行，以下將列出SUBSTR(ING)、LIKE+OR及Regular
Express等3種方法，不過建議採行第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的問題，就資源有限性及有效分配立場難以贊同，應多加思考如何在現有/簡單化之模式之下解決問題。
參考網站
沒有留言:
張貼留言