利用ORACLE的MINUS函式和OVER函式,直接通過檢視實現兩個記錄集的比較。(轉載)
1 前言
-------------------------------------------------------------
在程式設計過程中,往往遇到比較兩個記錄集的差異。如,判斷原來傳入的訂單資料與後來傳入的訂單資料之間的差異,並且將差異的資料顯示給使用者。
實現的方式有多種,如程式設計儲存過程返回遊標,在儲存過程中對兩批資料進行比較...等等,當然返回差異資料的方式多種多樣,既可以是遊標,又可以臨時表或其它方式。
本文主要論述利用ORACLE的MINUS函式和OVER函式,直接通過檢視實現兩個記錄集的比較。
-------------------------------------------------------------
2 實現步驟
-------------------------------------------------------------
2.1 利用MINUS函式,判斷原始表與比較表的增量差異<設,兩個記錄集分別以表的方式存在,為表A和表B。其中,A表為原始表,B表為後來產生的比較表,即要與A表進行比較的資料表>
增量差異指,A中存在的記錄,哪些在B表中沒有的,也就是說,A表的記錄被修改或刪除
2.2 利用MINUS函式,判斷比較表與原始表的增量差異
即B表中存在的記錄,哪些在A表中沒有,也就是說,B表新增的或A表修改的記錄
2.3 連線A-B的增量差異表和B-A的增量差異表,利用OVER函式判斷資料重複的次數
如果資料重複次數為2,則該記錄的標識為“修改”;
如果資料重複次數為1,且出現在A-B的增量差異表中,則該記錄的標識為“刪除”;
如果資料重複次數為1,且出現在B-A的增量差異表中,則該記錄的標識為“新增”
-------------------------------------------------------------
3 例項演練
-------------------------------------------------------------
--3.1 建立資料表和例項環境<設原始記錄集為資料表A,比較記錄集為資料表B,當然實際應用過程中,參與比較的通常是檢視,不會是資料表>
--測試環境配置
Drop Table a;
Drop Table b;
Create Table a(a1 Numeric(28),a2 Varchar2(10));
Create Table b(b1 nUMERIC(28),b2 VarChar2(10));
Insert Into a Values (1,'a');
Insert Into a Values (2,'ba');
Insert Into a Values (3,'ca');
Insert Into a Values (4,'da');
Insert Into b Values (1,'a');
Insert Into b Values (2,'bba');
Insert Into b Values (3,'ca');
Insert Into b Values (5,'dda');
Insert Into b Values (6,'Eda');
Commit;
Select * from a;
Select * From b;
--3.2 建立比較檢視
Create Or replace View VW_Test_Minus as
--標識重複出現的次數(次數=1->刪除或新增,次數=2->修改)
SELECT A1
,a2
,t --A表/B表標識
,ROW_NUMBER() OVER (PARTITION BY A1 ORDER BY A1) RN --記錄重複次數
FROM
(
Select a1,a2,'A表' T --檢視A表存在,B表沒有的記錄(修改或刪除)
from
(
(Select * from a )
Minus
(Select * From b)
) a2b
Union --聯合A表與B表不相同的記錄集
Select b1,b2,'B表' T --檢視B表存在,A表沒有的記錄(修改或新增)
from
(
(Select * from b )
Minus
(Select * From a)
) b2a
) F;
/
--3.3 比較結果集
Select a1
,a2
,T
,Rn
,Decode(Rn --標識記錄變化
,2,'修改'
,Decode(T
,'A表','刪除'
,'新增')) Mark
From VW_Test_Minus
Where Rn=(Select Count(*) From VW_Test_Minus V Where V.a1=VW_Test_Minus.a1)
;
-------------------------------------------------------------
4 後記
-------------------------------------------------------------
許多DBA都特別痛恨那些希望通過一句SELECT語句來實現複雜使用者需求的編碼人員,使用MINUS和OVER函式來實現資料比較,在執行效率上,可能會存在問題。
本文的目的,並不在於討論程式執行的效率,而在於拋磚引玉,引起大家對OVER函式的重視和對MINUS函式的認知。
-------------------------------------------------------------
在程式設計過程中,往往遇到比較兩個記錄集的差異。如,判斷原來傳入的訂單資料與後來傳入的訂單資料之間的差異,並且將差異的資料顯示給使用者。
實現的方式有多種,如程式設計儲存過程返回遊標,在儲存過程中對兩批資料進行比較...等等,當然返回差異資料的方式多種多樣,既可以是遊標,又可以臨時表或其它方式。
本文主要論述利用ORACLE的MINUS函式和OVER函式,直接通過檢視實現兩個記錄集的比較。
-------------------------------------------------------------
2 實現步驟
-------------------------------------------------------------
2.1 利用MINUS函式,判斷原始表與比較表的增量差異<設,兩個記錄集分別以表的方式存在,為表A和表B。其中,A表為原始表,B表為後來產生的比較表,即要與A表進行比較的資料表>
增量差異指,A中存在的記錄,哪些在B表中沒有的,也就是說,A表的記錄被修改或刪除
2.2 利用MINUS函式,判斷比較表與原始表的增量差異
即B表中存在的記錄,哪些在A表中沒有,也就是說,B表新增的或A表修改的記錄
2.3 連線A-B的增量差異表和B-A的增量差異表,利用OVER函式判斷資料重複的次數
如果資料重複次數為2,則該記錄的標識為“修改”;
如果資料重複次數為1,且出現在A-B的增量差異表中,則該記錄的標識為“刪除”;
如果資料重複次數為1,且出現在B-A的增量差異表中,則該記錄的標識為“新增”
-------------------------------------------------------------
3 例項演練
-------------------------------------------------------------
--3.1 建立資料表和例項環境<設原始記錄集為資料表A,比較記錄集為資料表B,當然實際應用過程中,參與比較的通常是檢視,不會是資料表>
--測試環境配置
Drop Table a;
Drop Table b;
Create Table a(a1 Numeric(28),a2 Varchar2(10));
Create Table b(b1 nUMERIC(28),b2 VarChar2(10));
Insert Into a Values (1,'a');
Insert Into a Values (2,'ba');
Insert Into a Values (3,'ca');
Insert Into a Values (4,'da');
Insert Into b Values (1,'a');
Insert Into b Values (2,'bba');
Insert Into b Values (3,'ca');
Insert Into b Values (5,'dda');
Insert Into b Values (6,'Eda');
Commit;
Select * from a;
Select * From b;
--3.2 建立比較檢視
Create Or replace View VW_Test_Minus as
--標識重複出現的次數(次數=1->刪除或新增,次數=2->修改)
SELECT A1
,a2
,t --A表/B表標識
,ROW_NUMBER() OVER (PARTITION BY A1 ORDER BY A1) RN --記錄重複次數
FROM
(
Select a1,a2,'A表' T --檢視A表存在,B表沒有的記錄(修改或刪除)
from
(
(Select * from a )
Minus
(Select * From b)
) a2b
Union --聯合A表與B表不相同的記錄集
Select b1,b2,'B表' T --檢視B表存在,A表沒有的記錄(修改或新增)
from
(
(Select * from b )
Minus
(Select * From a)
) b2a
) F;
/
--3.3 比較結果集
Select a1
,a2
,T
,Rn
,Decode(Rn --標識記錄變化
,2,'修改'
,Decode(T
,'A表','刪除'
,'新增')) Mark
From VW_Test_Minus
Where Rn=(Select Count(*) From VW_Test_Minus V Where V.a1=VW_Test_Minus.a1)
;
-------------------------------------------------------------
4 後記
-------------------------------------------------------------
許多DBA都特別痛恨那些希望通過一句SELECT語句來實現複雜使用者需求的編碼人員,使用MINUS和OVER函式來實現資料比較,在執行效率上,可能會存在問題。
本文的目的,並不在於討論程式執行的效率,而在於拋磚引玉,引起大家對OVER函式的重視和對MINUS函式的認知。
http://www.cnblogs.com/lizw/archive/2007/04/26/729006.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14710393/viewspace-751343/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【函式】oracle視窗函式over()的理解函式Oracle
- Oracle 中 replace函式和translate函式比較Oracle函式
- oracle over函式 詳解(轉)Oracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- 幾個分析函式的比較函式
- javascript函式過載的實現JavaScript函式
- 【Mysql】Mysql似oracle分析函式sum over的實現MySqlOracle函式
- Oracle over()函式使用Oracle函式
- Mysql 一個比較好用的函式MySql函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- C++ 函式過載,函式模板和函式模板過載,選擇哪一個?C++函式
- Oracle Decode()函式和CASE語句的比較Oracle函式
- C++宏和函式的比較C++函式
- js實現函式過載JS函式
- 字串比較的常用函式字串函式
- sum()over()和count()over()分析函式函式
- oracle實驗記錄 (函式index)Oracle函式Index
- js匿名函式和具名函式執行效率比較JS函式
- Oracle統計分析函式集,over(partition by..) 的運用Oracle函式
- Oracle 語法之 OVER (PARTITION BY ..) 及開窗函式 轉載Oracle函式
- c語言中通過函式指標實現函式過載C語言函式指標
- JavaScript - 函式 setTimeout 和 setInterval 的比較JavaScript函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- PHP中實現函式過載PHP函式
- javascript如何實現函式過載JavaScript函式
- [Oracle] minus 和 not exists比較Oracle
- PHP的壓縮函式實現:gzencode、gzdeflate和gzcompress比較PHP函式
- 分析函式 over函式
- 呼叫函式實現兩個數交換函式
- linux下execl函式的使用和比較Linux函式
- excel 字元比較函式Excel字元函式
- django 的類檢視和函式檢視-雜談Django函式
- 函式過載與函式模板的區別函式
- [轉]oracle日期函式集錦Oracle函式
- python中實現函式過載Python函式
- javascript模擬實現函式過載JavaScript函式
- PHP中實現函式過載薦PHP函式
- SQLServer和Oracle的常用函式對比SQLServerOracle函式