產品資料表
|
兩種商品組合(300元以內)
| ||||||||||||||||||||||||||||||||||||||||||||||||
|  |
|
為達到前述之結果，需針對產品資料表中任二種不同的商品逐一進行組合並計算價格總合，將符合商品總價為300元以下的商品組合列出於右側表格。將前述整理出運算重點並說明如下：
1. 二種不同商品組合
為達成商品組合的目的，可採用自我連接(SELF JOIN)的方式將產品表格連接到自己，將產品資料表之資料進行兩兩組合，其作法在FROM為FROM #PROD P1, #PROD P2，產品資料表以P1及P2兩個別名(Alias)進行連結(JOIN)，即可產生兩產品的組合。
另外，對於產品組合中兩者為相同產品之組合，因不符合不同產品的定義故予以剔除。
即下列SQL中的P1.ProdId <> P2.ProdId。
2. 總價格必需符合特定價格
另一項重點，在於兩種商品組合其總價格需符合特定價格(即300元)。即下述SQL中的P1.Price + P2.Price <=300。
綜合前述，將SQL整理及所產生的結果
SELECT P1.ProdId "P1 Prod"
, P2.ProdId "P2 Prod "
, P1. Price "P1 Price"
, P2. Price "P2 Price"
, P1. Price +P2. Price "Price"
FROM #PROD P1, #PROD P2
WHERE 1=1
AND P1.ProdId <> P2.ProdId
AND P1.Price + P2.Price <=300
ORDER BY 1, 2
No
|
P1 Prod
|
P2 Prod
|
P1 Price
|
P2 Price
|
Price
|
1
|
A
|
C
|
200
|
100
|
300
|
2
|
C
|
A
|
100
|
200
|
300
|
3
|
C
|
D
|
100
|
190
|
290
|
4
|
C
|
I
|
100
|
180
|
280
|
5
|
C
|
J
|
100
|
160
|
260
|
6
|
D
|
C
|
190
|
100
|
290
|
7
|
I
|
C
|
180
|
100
|
280
|
8
|
J
|
C
|
160
|
100
|
260
|
由上述結果中之1及2(即灰底)為相同產品組合資料，但組合順序不同(A-C, C-A)，同樣的第3與第6(C-D, D-C)、4與7(C-I, I-C) 以及5與8(C-J, J-C)也是組合順序不同之相同產品組合，對於產品相同但順序不同的重覆資料應予以剔除，因此，需將第一項重點中『不同商品組合』條件式進行修正，只保留商品組合中第1種產品編號小於第2種商品的組合，此方式除可避免重複的問題外，另一項重要因素是為撰寫複雜度的問題，當產品組合數量增加時，若採用原來不等於(即<>)的篩選條件，條件數量會依組合數目的增加而呈指數型成長模式(公式為 
, n:產品組合數量)，下列表格將以2、3及4種產品組合為範例說明：
, n:產品組合數量)，下列表格將以2、3及4種產品組合為範例說明：
產品組合數量
|
SQL
|
不等式數量
|
2種
|
WHERE 1=1
AND P1.ProdId <> P2.ProdId
|
1
|
3種
|
WHERE 1=1
AND P1.ProdId <> P2.ProdId
AND P1.ProdId <> P3.ProdId
AND P2.ProdId <> P3.ProdId
|
3
|
4種
|
WHERE 1=1
AND P1.ProdId <> P2.ProdId
AND P1.ProdId <> P3.ProdId
AND P1.ProdId <> P4.ProdId
AND P2.ProdId <> P3.ProdId
AND P2.ProdId <> P4.ProdId
AND P3.ProdId <> P4.ProdId
|
6
|
為避免前述兩個重要問題，將 SQL改寫如下(為考量可讀性，將產品組合有依序排列):
SELECT P1.ProdId "P1 Prod"
, P2.ProdId "P2 Prod "
, P1.Price "P1 Price"
, P2.Price "P2 Price"
, P1.Price +P2.Price "Price"
FROM #PROD P1, #PROD P2
WHERE 1=1
AND P1.ProdId < P2.ProdId --修正
AND P1.Price + P2.Price <=300
ORDER BY 1, 2
5種產品(多種產品)
先前是介紹2種產品組合之作法，而當產品組合增加時，資料之組合空間將大幅成長，在此將探討多種產品組合的其他作法，將以總價不得超過850元之5種(多種)不同商品組合為例，並以二種方法進行說明，首先運用先前範例中任2種商品組合之方法，即採用SELF JOIN的方式產生；另外，運用SQL Server 2005所提供之一般資料表運算式(Common Table Expression: CTE)來達成，分別說明如下:
方法1: SELF JOIN(自我連接)
SELECT
'[' + P1.ProdId + ']'
+ ',[' + P2.ProdId + ']'
+ ',[' + P3.ProdId + ']'
+ ',[' + P4.ProdId + ']'
+ ',[' + P5.ProdId + ']' List
, P1.ProdId "P1"
, P2.ProdId "P2"
, P3.ProdId "P3"
, P4.ProdId "P4"
, P5.ProdId "P5"
, P1.Price +P2.Price + P3.Price +P4.Price + P5.Price "Price"
FROM #PROD P1
, #PROD P2
, #PROD P3
, #PROD P4
, #PROD P5
WHERE 1=1
AND P1.ProdId < P2.ProdId
AND P2.ProdId < P3.ProdId
AND P3.ProdId < P4.ProdId
AND P4.ProdId < P5.ProdId
AND P1.Price +P2.Price + P3.Price +P4.Price + P5.Price <=850
ORDER BY 1, 2, 3, 4, 5
List
|
P1
|
P2
|
P3
|
P4
|
P5
|
Price
|
[A],[C],[D],[I],[J]
|
A
|
C
|
D
|
I
|
J
|
830
|
[A],[C],[H],[I],[J]
|
A
|
C
|
H
|
I
|
J
|
850
|
[C],[D],[H],[I],[J]
|
C
|
D
|
H
|
I
|
J
|
840
|
方法 2: 一般資料表運算式(Common Table Expression)
前述曾以2種及5組商品組合為範例，對增加產品組合數目的概念而言，與一般程式語言之迴圈(Loop)類似，本範例將應用SQL Server 2005所提供之新功能以遞迴(Recursive)方式逐一增加產品至產品組合中，將一般資料表運算式(Common Table Expression: CTE)進行以遞迴處理，其SQL及產生結果如下:
WITH STORE (N, PreProdId, List, Price, P1, P2, P3, P4, P5) AS
(
--1. 定錨區(Anchor)
SELECT 1 AS N
, ProdId AS PreProdId
, CAST(( '[' + ProdId +']') AS VARCHAR(200)) AS List
, Price
, ProdId AS P1
, CAST(NULL AS VARCHAR(2)) AS P2 --為維持型態一致, 將NULL轉換為VARCHAR(2)
, CAST(NULL AS VARCHAR(2)) AS P3
, CAST(NULL AS VARCHAR(2)) AS P4
, CAST(NULL AS VARCHAR(2)) AS P5
FROM #PROD
WHERE 1=1
AND Price < 850
UNION ALL
--2. 遞迴呼叫區
SELECT N+1 AS N
, P.ProdId AS PreProdId
, CAST((S. List +',' + '[' + P.ProdId +']') AS VARCHAR(200)) AS List
, S.Price +P.Price AS Price
, P1
, CASE WHEN N=1 THEN P.ProdId ELSE P2 END AS P2
, CASE WHEN N=2 THEN P.ProdId ELSE P3 END AS P3
, CASE WHEN N=3 THEN P.ProdId ELSE P4 END AS P4
, CASE WHEN N=4 THEN P.ProdId ELSE P5 END AS P5
FROM STORE S, #PROD P
WHERE 1=1
AND N<5
AND P.Price <= 850 - S.Price
AND S.PreProdId < P.ProdId
)
--3. 結果區
SELECT *
FROM STORE
WHERE 1=1
AND N = 5
N
|
PreProdId
|
List
|
Price
|
P1
|
P2
|
P3
|
P4
|
P5
|
5
|
J
|
[C],[D],[H],[I],[J]
|
840
|
C
|
D
|
H
|
I
|
J
|
5
|
J
|
[A],[C],[H],[I],[J]
|
850
|
A
|
C
|
H
|
I
|
J
|
5
|
J
|
[A],[C],[D],[I],[J]
|
830
|
A
|
C
|
D
|
I
|
J
|
上述SQL是應用一般資料表運算式，關於一般資料表運算式的詳細說明部分請參考該章節之說明，細節部分本節將不再贅述。以下將分成定錨區(Anchor)、遞迴呼叫區及結果區等，分別說明:
欄位
|
型態
|
定錨區(Anchor)
|
遞迴呼叫區
|
N
|
INT
|
1
|
N+1
|
PreProdId
|
VARCHAR(2)
|
ProdId
|
ProdId
|
List
|
VARCHAR(200)
|
CAST(( '[' + ProdId +']') AS VARCHAR(200))
|
CAST((S. List +',' + '[' + P.ProdId +']') AS VARCHAR(200))
|
Price
|
INT
|
Price
|
S.Price +P.Price
|
P1
|
VARCHAR(2)
|
ProdId
|
P1
|
P2~P5
|
VARCHAR(2)
|
CAST(NULL AS VARCHAR(2))
|
CASE WHEN N=1 THEN P.ProdId ELSE P2 END AS P2
|
1. 定錨區(Anchor)
定錨區是為遞迴呼叫起點，在此將粗略用總價進行篩選。此處重點在於後續執行所需參考之虛擬欄位:
n N (產品組合數量)
N是為產品組合數量，初始值為1，即初始化只有1種產品。
n PreProdId
商品組合中前一商品編號之虛擬欄位(PreProdId)。
n List (組合產品編碼)
List是組合產品之產品編碼。
n P1~P5
商品編號，因定錨區為各商品組合中第1項商品，因此P1為產品編碼，其餘將為NULL值，但SQL Server一般資料表運算式(CTE)對定錨區及遞迴呼叫區的欄位型態及精度(包含長度)需完全一致的限制，因此將NULL值轉換與ProdId相同的定義(型態及長度)，即CAST(NULL AS VARCHAR(2))。
SELECT 1 AS N
, ProdId AS PreProdId
, CAST(( '[' + ProdId +']') AS VARCHAR(200)) AS List
, Price
, ProdId AS P1
, CAST(NULL AS VARCHAR(2)) AS P2
, CAST(NULL AS VARCHAR(2)) AS P3
, CAST(NULL AS VARCHAR(2)) AS P4
, CAST(NULL AS VARCHAR(2)) AS P5
FROM #PROD
WHERE 1=1
AND Price < 850
N
|
PreProdId
|
List
|
Price
|
P1
|
P2
|
P3
|
P4
|
P5
|
1
|
A
|
[A]
|
200
|
A
| ||||
1
|
B
|
[B]
|
250
|
B
| ||||
1
|
C
|
[C]
|
100
|
C
| ||||
1
|
D
|
[D]
|
190
|
D
| ||||
1
|
E
|
[E]
|
230
|
E
| ||||
1
|
F
|
[F]
|
320
|
F
| ||||
1
|
G
|
[G]
|
290
|
G
| ||||
1
|
H
|
[H]
|
210
|
H
| ||||
1
|
I
|
[I]
|
180
|
I
| ||||
1
|
J
|
[J]
|
160
|
J
|
另外，在產生產品組合中採用兩次資料轉換的作法，是為符合一般資料表運算式的條件，即定錨及遞迴呼叫區中所有欄位，其型態及精度(包含長度)需完全一致的限制，因此SQL為CAST(( '[' + ProdId +']') AS VARCHAR(200))。
2. 遞迴呼叫區
遞迴呼叫區是整組SQL的關鍵，既然稱為『遞迴呼叫』則與一般程式的遞迴觀念類似，則時主要將將產品逐一增加至產品組合中，並依照結束條件及遞迴限制式等規範，在SQL中除考慮增加產品也需考量原產品組合的記錄。
虛擬欄位
n N (產品組合數量)
N+1(N+1 èN)為產品組合數量，當每增加1種產品時，則N會加1。
n PreProdId
商品組合中前一商品編號之虛擬欄位(PreProdId) 。
n List (組合產品編碼)
產品組合編碼需考慮原產品組合以及新產品增加後之影響CAST((S. List +',' + '[' + CAST(P.ProdId AS VARCHAR) +']') AS VARCHAR(200))List是組合產品之產品編碼。
n P1~P5
商品編號，因定錨區為各商品組合中第1項商品，因此P1為產品編碼，其餘將為NULL值，但SQL Server一般資料表運算式(CTE)對定錨區及遞迴呼叫區的欄位型態及精度(包含長度)需完全一致的限制，因此將NULL值轉換與ProdId相同的定義(型態及長度)，即CAST(NULL AS VARCHAR(2))。
遞迴結束及限制式
n 結束條件
結束條件為產品組合後數目為5，故條件為N<5 o:p="">
n 遞迴限制式
ü 新增商品之價格限制
在原組合中所允許增加商品，必需符合產品組合總價限制即850元，因此將以P.Price <= 850 - S.Price為限制式。
ü 新增商品之產品編碼限制
在前面SELF JOIN方法中為避免產品組合因順序不同而造成資料重複的作法，其觀念此處仍可適用，因此商品組合中前一商品的產品編號需小於後者，其限制式為S.PreProdId < P.ProdId。
SELECT N+1 AS N
, P.ProdId AS PreProdId
, CAST((S. List +',' + '[' + CAST(P.ProdId AS VARCHAR) +']') AS VARCHAR(200)) AS List
, S.Price +P.Price AS Price
, P1
, CASE WHEN N=1 THEN P.ProdId ELSE P2 END AS P2
, CASE WHEN N=2 THEN P.ProdId ELSE P3 END AS P3
, CASE WHEN N=3 THEN P.ProdId ELSE P4 END AS P4
, CASE WHEN N=4 THEN P.ProdId ELSE P5 END AS P5
FROM STORE S, #PROD P
WHERE 1=1
AND N<5
AND P.Price <= 850 - S.Price
AND S.PreProdId < P.ProdId
3. 結果區
當前束之初始及遞迴呼叫結束後，則在CTE暫存區中會儲存初始資料運算暫存及結果集，在結果區主要是將運算暫存資料剔除，以顯示所需之運算結果，本範例是取得5種產品之產品組合，因此只取出N=5之產品組合資料集，SQL如下所示:
SELECT *
FROM STORE
WHERE 1=1
AND N = 5
沒有留言:
張貼留言