程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 巧妙利用PARTITION分組排名遞增特性解決合並連續相同數據行,巧妙利用partition

巧妙利用PARTITION分組排名遞增特性解決合並連續相同數據行,巧妙利用partition

編輯:更多數據庫知識

巧妙利用PARTITION分組排名遞增特性解決合並連續相同數據行,巧妙利用partition


問題提出

先造一些測試數據以說明題目:

DECLARE @TestData TABLE(ID INT,Col1 VARCHAR(20),Col2 VARCHAR(20)) 
INSERT INTO @TestData(ID,Col1,Col2) 
SELECT 1,'New','Approved' UNION ALL 
SELECT 2,'Approved','Commited' UNION ALL 
SELECT 3,'Commited','In Progress' UNION ALL 
SELECT 4,'New','Approved' UNION ALL 
SELECT 5,'New','Approved' UNION ALL 
SELECT 6,'New','Approved' UNION ALL 
SELECT 7,'Approved','Removed' 
SELECT * FROM @TestData

數據說明,ID列連續自增,列1和列2都是TFS中PBI的狀態記錄,就是從什麼變更到什麼,如新建到批准,批准到提交神馬的

現在要求連續且相同的狀態變更記錄合並,不連續或者不同的狀態變更保留,例如:

以上圖為例,ID為1,4,5,6的記錄都是從New到Approved狀態,但是記錄1與記錄4、5、6不相鄰,或者說不連續,那麼就要分成兩組,

記錄1一組,記錄4、5、6一組,其它記錄因為狀態變更不相同所以全部保留,最後的查詢結果應該長成下圖這個樣子:

繼續之前你可以先自己試下,這可能會帶來新的解題思路,

解題思路

該問題的關鍵在於GROUP BY會把記錄1、4、5、6合並在一起,而這不符合要求,僅需要合並4、5、6,源表裡沒有這樣一個字段可以將記錄1與記錄4、5、6區分開來,這是解題的關鍵

這裡可以利用RANK函數配合使用PARTITION關鍵字,首先把1456分到一組去,同時產生一個組內排名的新字段R,這個排名R很關鍵,後邊會用到,見下圖:

RANK函數不了解的點這裡

RANK函數以Col1 + Col2為分組條件,這樣分成了四組,分別是New-Approved、Approved-Commited、Commited-In Progress、Approved-Removed

在New-Approved組內,記錄1、4、5、6分別排名1、2、3、4;其它組內僅一條記錄,在其組內排名均為1

現在制造了一個R字段,R字段標識了每條記錄在其組內的排名,排名自1開始遞增,

源表內ID自增,組內排名R遞增,這就是解題的關鍵,

當連續相同的記錄出現時,其ID與其排名R在同時遞增,則其差值是相同的,拿到這個差值就可以很容易解決題目了,看下圖:

記錄4、5、6相同且連續出現,其ID與其排名在同時增長,其差值則保持不變,這裡使用Col1 + Col2 + Gap作為分組條件即可將記錄4、5、6合並,再取個最小ID出來,問題解決,完整腳本如下:

可是如果ID不連續時怎麼辦呢?這個不難,參考[MSSQL]ROW_NUMBER函數


excel怎分組排序編號? 把一列數據裡所有相同的數據編同一個序號,序號要遞增

A2中輸入=INT((ROW()+1)/3)。。。向下拉就好了!
 

excel怎分組編號? 把一列數據裡所有相同的數據編同一個序號,序號要遞增

看不出更多規律來,猜一個:
A1=03160011
=text(right(A1,2)-10,"0000")

下拉填充
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved