Posted on 2007-09-04 11:37
大大毛 阅读(337)
评论(0) 编辑 收藏 所属分类:
SQL
問題
通常來說實現表級的關聯以及在列之間實現運算還算是比較容易，可是利用SQL來實現行間的關聯運算就顯得比較困難，這裏就一個實例來解析如何來思考及解決此類的問題。
表結構
Create
Table
tb_ExampleA (
kID
Char
(
10
),
--
合約ID,主KEY
iYear
Int
,
--
第幾年度
iTime
Int
,
--
第幾期
D1
Int
,
--
開始日期
PayMode
Char
(
1
),
--
付款方式
cOther
Char
(
100
),
--
內容
Primary
Key
(kID,iYear,iMonth)
);
表結構說明
kID 主關鍵字，例如合約號
iYear 第幾年度( >= 1)，連續遞增
iTime 第幾期[1, 12]，連續遞增，根據付款方式有所不同
D1 開始日期，數字類型的民國日期
PayMode 付款方式，取值 A--年繳，S--半年繳，Q--季繳，M--月繳
要求
找到那些 D1 有問題的合約。因為根據PayMode來說相同 kID 的 D1 變化是存在有規律的，例如如果是A--年繳則應該是一年一跳，M--月繳則應該是一個月一跳，因此要找到那些跳的間隔有錯的合約。
數據現狀
iYear / iTime 兩列上的數據就很亂，有跳期的現象存在，例如月繳1年1期--1年3期。
D1 列上有誤，例如月繳方式下 950505 -- 950718 (正確應該是950505 -- 959604)
表結構與
我的上一篇
示例2中所用表完全相同，只不過上一篇是為了找出年度+月份不連續的問題件，而這裏則是要解決數據行Row之間的對比，不過這一次還是要用到上回講述到的那些解決方案，只不過要更加複雜。
思考
1. 要達到間隔的計算，就要實現行間數據的對比，因此大體的思路應該是使用自連接的方式來完成。
2. 根據數據現狀可以看出，上一篇中提到的將 iYear + iTime 兩列合併的方案已經不可用，因為根據現狀這兩列雖然保持唯一和增加，但是增加的 Step(步長) 已經不可信任。根據自連接的特點，我們必須找到一個絕對可以信賴的遞增列，然後再在該列上實現自連接，問題是這一列該如何選取呢？
3. D1 列是數字值的民國日期，要用日期的間隔來判斷需要先將該列轉換成正常的西元日期型列才可以實現。
解決方法
1. 找到那個 Step 可以信賴的遞增列。
既然 iYear + iTime 的 Step 不可信賴可是兩列唯一和增加還是可用的，因為可以用 自行構建Identity的方法 來據此生成一個可用的 Identity 列。
2. D1的西元日期型轉換，可以用 幾個用於SQL的日期轉換函數(vb) 中功能2所示的 parseDate(strSQL) 來實現。
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語句可以看到為了實現自連接，構建了2張表 AL(2-14行) 和 AR(16-28行)，兩表的內容完全一致，中間使用了自定義 Identity 列 Index1 (4-11行) ，然後再將AL 與 AR 實現錯值連接(29行)。
對連接後表記錄的日期間隔運算，放在Where子句中(30-38行)，這裏寫的是僞語句，需要用前面提到的 VB函數轉換才可以運行，不過這樣看起來就會明子許多。
可以看到，只要在處理裏靈活運用各種技巧就可以解決更加複雜的邏輯。