問題提出
先造一些測試資料以說明題目:
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
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5iM4IjN1IjMxQjMwMTM3AzLchDM0EDMy8CX1IzM3IzLcd2bsJ2Lc12bj5ycn9Gbi52YuAzcldWYtl2Lc9CX6MHc0RHaiojIsJye.png)
資料說明,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函數
------------------------------------------
除非特别聲明,文章均為原創,版權與部落格園共有,轉載請保留出處
BUY ME COFFEE