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