Posted on 2007-09-04 11:37
大大毛 閱讀(360)
評論(0) 編輯 收藏 所屬分類:
SQL
問題
??????通常來說實現(xiàn)表級的關(guān)聯(lián)以及在列之間實現(xiàn)運算還算是比較容易，可是利用SQL來實現(xiàn)行間的關(guān)聯(lián)運算就顯得比較困難，這裏就一個實例來解析如何來思考及解決此類的問題。
表結(jié)構(gòu)
Create
?
Table
?tb_ExampleA?(
??kID?????
Char
(
10
),???
--
合約ID,主KEY
??iYear???
Int
,????????
--
第幾年度
??iTime ??
Int
,????????
--
第幾期
??D1??????
Int
,????????
--
開始日期
??PayMode?
Char
(
1
),????
--
付款方式
??cOther??
Char
(
100
),??
--
內(nèi)容
??
Primary
?
Key
(kID,iYear,iMonth)
);
??????表結(jié)構(gòu)說明
?????????kID???????????????主關(guān)鍵字，例如合約號
?????????iYear????????????第幾年度( >= 1)，連續(xù)遞增
?????????iTime???????????第幾期[1, 12]，連續(xù)遞增，根據(jù)付款方式有所不同
?????????D1??????????????????開始日期，數(shù)字類型的民國日期
?????????PayMode???付款方式，取值 A--年繳，S--半年繳，Q--季繳，M--月繳
??????要求
?????????找到那些 D1 有問題的合約。因為根據(jù)PayMode來說相同 kID?的 D1 變化是存在有規(guī)律的，例如如果是A--年繳則應(yīng)該是一年一跳，M--月繳則應(yīng)該是一個月一跳，因此要找到那些跳的間隔有錯的合約。
??????數(shù)據(jù)現(xiàn)狀
?????????iYear / iTime?兩列上的數(shù)據(jù)就很亂，有跳期的現(xiàn)象存在，例如月繳1年1期--1年3期。
?????????D1 列上有誤，例如月繳方式下??950505 -- 950718?(正確應(yīng)該是950505 -- 959604)
??????表結(jié)構(gòu)與
我的上一篇
?示例2中所用表完全相同，只不過上一篇是為了找出年度+月份不連續(xù)的問題件，而這裏則是要解決數(shù)據(jù)行Row之間的對比，不過這一次還是要用到上回講述到的那些解決方案，只不過要更加複雜。
思考
??????1. 要達到間隔的計算，就要實現(xiàn)行間數(shù)據(jù)的對比，因此大體的思路應(yīng)該是使用自連接的方式來完成。
??????2. 根據(jù)數(shù)據(jù)現(xiàn)狀可以看出，上一篇中提到的將 iYear + iTime 兩列合併的方案已經(jīng)不可用，因為根據(jù)現(xiàn)狀這兩列雖然保持唯一和增加，但是增加的 Step(步長) 已經(jīng)不可信任。根據(jù)自連接的特點，我們必須找到一個絕對可以信賴的遞增列，然後再在該列上實現(xiàn)自連接，問題是這一列該如何選取呢？
??????3. D1 列是數(shù)字值的民國日期，要用日期的間隔來判斷需要先將該列轉(zhuǎn)換成正常的西元日期型列才可以實現(xiàn)。
解決方法
??????1. 找到那個 Step 可以信賴的遞增列。
?????????既然 iYear + iTime?的 Step 不可信賴可是兩列唯一和增加還是可用的，因為可以用 自行構(gòu)建Identity的方法?來據(jù)此生成一個可用的 Identity 列。
??????2. D1的西元日期型轉(zhuǎn)換，可以用 幾個用於SQL的日期轉(zhuǎn)換函數(shù)(vb) 中功能2所示的 parseDate(strSQL) 來實現(xiàn)。
??????3. 問題日期的間隔這裏只簡單的按大於32天/月來判斷。
SQL語句
SQL語句
?1?Select?*?From
?2???(Select
?3?????????A0.*,
?4?????????(Select?
?5?????????????Count(*)?
?6???????????From?
?7?????????????LIB/tb_ExampleA?subA
?8???????????Where?
?9?????????????subA.kID?=?A0.kID
10?????????????And?subA.iYear*100?+?subA.iTime?<=?A0.iYear*100?+?A0.iTime
11?????????)?Index1
12???????From
13?????????LIB/tb_ExampleA?A0
14???)?AL
15???Inner?Join
16???(Select
17?????????A0.*,
18?????????(Select
19?????????????Count(*)
20???????????From
21?????????????LIB/tb_ExampleA?subA
22???????????Where
23?????????????subA.kID?=?A0.kID
24?????????????And?subA.iYear*100?+?subA.iTime?<=?A0.iYear*100?+?A0.iTime
25?????????)?Index1
26???????From
27?????????LIB/tb_ExampleA?A0
28???)?AR
29???On?AL.kID?=?AR.kID?And?AL.Index1?=?AR.Index1?+?1
30???Where
31?????@Date(AR.D1)?+?(
32???????CASE?AR.PayMode
33?????????WHEN?'A'?THEN?12
34?????????WHEN?'S'?THEN?6
35?????????WHEN?'Q'?THEN?3
36?????????ELSE?1
37???????END?*?32
38?????)?Days?<?@Date(AL.D1)
?????????從該SQL語句可以看到為了實現(xiàn)自連接，構(gòu)建了2張表 AL(2-14行) 和 AR(16-28行)，兩表的內(nèi)容完全一致，中間使用了自定義 Identity 列 Index1 (4-11行) ，然後再將AL 與 AR 實現(xiàn)錯值連接(29行)。
?????????對連接後表記錄的日期間隔運算，放在Where子句中(30-38行)，這裏寫的是僞語句，需要用前面提到的 VB函數(shù)轉(zhuǎn)換才可以運行，不過這樣看起來就會明子許多。
?????????可以看到，只要在處理裏靈活運用各種技巧就可以解決更加複雜的邏輯。