Posted on 2007-09-03 19:31
大大毛 閱讀(459)
評論(0) 編輯 收藏 所屬分類:
SQL
問題：
??????經常會遇到在業(yè)務邏輯上數(shù)據連續(xù)的而需要找到那些不連續(xù)數(shù)據的情況。
??????解決此類問題的方法有很多，可以根據具體的數(shù)據結構來加以選擇運用，現(xiàn)就示例說明：
示例1：
表A結構
Create
?
Table
?tb_ExampleA?(
??kID?????
Char
(
3
),??????????
--
主關鍵字
??iNo?????
Int
?
Not
?
Null
,?????
--
序號
??cContent?
varchar
(
100
),
??
Primary
?
Key
?(kID,iNo)
);
???表結構說明
??????kID??????主關鍵字，例如合同號
??????iNo??????序號(>=1)，從業(yè)務邏輯上來說對於同一筆合同號該列的值應該保持連續(xù)遞增
???要求
??????找到那些有問題的 kID
解決方法：
??????表A屬於是一種比較單純、簡單的結構，對於這類只要直接在表上做一次處理即可。
??????1. 方法A --?自連接
?????????利用對自身表的 iNo?進行錯值(+1或-1)外連,?找到無法連接的數(shù)據。
自連接
Select
????
Distinct
?t1.kID
??
From
????tb_ExampleA?t1
????
Left
?
Join
?tb_ExampleA?t2?
On
?t1.kID?
=
?t2.kID?
And
?t1.iNo?
=
?t2.iNo?
+
?
1
??
Where
????t1.iNo?
>
?
1
????
And
?t2.iNo?
Is
?
Null
??????這樣就可以定位到那些有問題的 kID 上。
??????這種解決方法是最通用的方法，不過在編寫上比較複雜。
??????2. 方法B -- 利用Count(*)
利用Count(*)
Select
????kID
??
From
????tb_ExampleA
??
Group
?
By
????kID
??
Having
????
Count
(
*
)?
<>
?
Max
(iNo)
??????看看這個實現(xiàn)就可以發(fā)現(xiàn)，這裏比較巧妙的利用了 iNo 列上的邏輯規(guī)則 (int型的連續(xù)遞增)，如果 iNo 不從1開始計數(shù)則在Having 子句中加一點點運算即可。
??????這個解決方法比較巧妙，執(zhí)行效率較高。
??????3. 方法C --?利用函數(shù)
利用公式運算
Select
????kID
??
From
????tb_ExampleA
??
Group
?
By
????kID
??
Having
????
Sum
(iNo)?
<>
?(
Max
(iNo)?
*
?(
Max
(iNo)?
+
?
1
))?
/
?
2
??????方法C 與方法B 的思路實際上是一致的，就是利用 Group?，在 iNo?列上直接實施檢查，方法C 功能更加強大一些，因為如果 iNo 的規(guī)則有變更的話(如奇數(shù)遞增, :P )，改改這裏用的 n(n+1)?/ 2 的公式就好。
示例2：
??????這裏是另外一種情況，相比示例1 來說更加複雜一些，它的 iNo 列由多列組成，示例如下。
表B結構
Create
?
Table
?tb_ExampleB?(
??kID???????
Char
(
3
),????????
--
主關鍵字
??iYear?????
Int
?
Not
?
Null
,???
--
序1
??iMonth????
Int
?
Not
?
Null
,???
--
序2
??cContent??
Varchar
(
100
),
??
Primary
?
Key
?(kID,iYear,iMonth)
);
??????表結構說明
?????????kID????????????主關鍵字，例如合同號
?????????iYear?????????第幾年度( >= 1)，連續(xù)遞增
?????????iMonth?????第幾月份[1, 12]，連續(xù)遞增
??????要求
?????????找到那些年度+月份不連續(xù)的 kID
解決方法：
??????表B 的結構與表A 相比在需要檢查的列上變成多列的結構，而且多列的內部還有著一些隱含關係(如這裏示例中的年/月，顯然不可能第1年度未滿12月就跳到第2年度)，不過就實質來看與表A 的結構一致 ( 可以將iYear , iMonth 看成一列?)，因此同樣可以適用示例1中的方法，只要多加合併?iYear,?iMonth的邏輯就好。
??????這裏對iYear, iMonth列的合併，使用自定義列 + 表封裝 的方式來實現(xiàn)，下面就示例上面的Count方式。?
利用Count(*)
Select
????kID
??From
????(Select?((iYear-1)*12?+?Month)?iNo,tb_ExampleB.*?From?tb_ExampleB)?tb_ExampleB
??Group?By
????kID
??Having
????Count(*)?<>?Max(iNo)