問(wèn)題提出
先造一些測(cè)試數(shù)據(jù)以說(shuō)明題目:
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
數(shù)據(jù)說(shuō)明,ID列連續(xù)自增,列1和列2都是TFS中PBI的狀態(tài)記錄,就是從什么變更到什么,如新建到批準(zhǔn),批準(zhǔn)到提交神馬的
現(xiàn)在要求連續(xù)且相同的狀態(tài)變更記錄合并,不連續(xù)或者不同的狀態(tài)變更保留,例如:
以上圖為例,ID為1,4,5,6的記錄都是從New到Approved狀態(tài),但是記錄1與記錄4、5、6不相鄰,或者說(shuō)不連續(xù),那么就要分成兩組,
記錄1一組,記錄4、5、6一組,其它記錄因?yàn)闋顟B(tài)變更不相同所以全部保留,最后的查詢結(jié)果應(yīng)該長(zhǎng)成下圖這個(gè)樣子:
繼續(xù)之前你可以先自己試下,這可能會(huì)帶來(lái)新的解題思路,
解題思路
該問(wèn)題的關(guān)鍵在于GROUP BY會(huì)把記錄1、4、5、6合并在一起,而這不符合要求,僅需要合并4、5、6,源表里沒(méi)有這樣一個(gè)字段可以將記錄1與記錄4、5、6區(qū)分開來(lái),這是解題的關(guān)鍵
這里可以利用RANK函數(shù)配合使用PARTITION關(guān)鍵字,首先把1456分到一組去,同時(shí)產(chǎn)生一個(gè)組內(nèi)排名的新字段R,這個(gè)排名R很關(guān)鍵,后邊會(huì)用到,見下圖:
RANK函數(shù)不了解的點(diǎn)這里
RANK函數(shù)以Col1 + Col2為分組條件,這樣分成了四組,分別是New-Approved、Approved-Commited、Commited-In Progress、Approved-Removed
在New-Approved組內(nèi),記錄1、4、5、6分別排名1、2、3、4;其它組內(nèi)僅一條記錄,在其組內(nèi)排名均為1
現(xiàn)在制造了一個(gè)R字段,R字段標(biāo)識(shí)了每條記錄在其組內(nèi)的排名,排名自1開始遞增,
源表內(nèi)ID自增,組內(nèi)排名R遞增,這就是解題的關(guān)鍵,
當(dāng)連續(xù)相同的記錄出現(xiàn)時(shí),其ID與其排名R在同時(shí)遞增,則其差值是相同的,拿到這個(gè)差值就可以很容易解決題目了,看下圖:
記錄4、5、6相同且連續(xù)出現(xiàn),其ID與其排名在同時(shí)增長(zhǎng),其差值則保持不變,這里使用Col1 + Col2 + Gap作為分組條件即可將記錄4、5、6合并,再取個(gè)最小ID出來(lái),問(wèn)題解決,完整腳本如下:
可是如果ID不連續(xù)時(shí)怎么辦呢?這個(gè)不難,參考[MSSQL]ROW_NUMBER函數(shù)
您可能感興趣的文章:- Mysql數(shù)據(jù)表分區(qū)技術(shù)PARTITION淺析
- sqlserver巧用row_number和partition by分組取top數(shù)據(jù)
- 分區(qū)軟件PartitionMagic的使用方法(images)及注意事項(xiàng)