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