SQL Server Rank() Over Partition w/Back 和 Forth 值

Question

我正在对 TFS 数据库使用以下查询。有问题的工作项从事件状态变为其他状态，然后返回事件状态。我想使用 Rank() Over Partition 将两组“事件”状态视为单独的组，而不是继续对第二个事件组进行编号，它在第一个事件组停止的地方继续编号。

;with cte as
(
    SELECT 
        dense_rank() over(partition by ID, State order by ID,  Rev) as rn
        , ID
        , Rev
        , State
        , Reason
        , NamePart
        --, *
    FROM dbo.WorkItemsWere Hist
        Left JOIN Constants Cs WITH (nolock)
            ON Hist.[Changed by] = Cs.ConstID

    WHERE ID = 38728
)
SELECT * 
FROM cte
--WHERE rn = 1
ORDER BY ID, Rev

下面是我得到的结果集，其中添加了一个额外的“Desired rn”列以显示我希望它成为什么:

Desired rn rn    ID          Rev         State                            
---------- ----- ----------- ----------- -----------------------------------
1          1     38728       1           Proposed                            
1          1     38728       2           Active                              
2          2     38728       3           Active                              
3          3     38728       4           Active                              
4          4     38728       5           Active                              
5          5     38728       6           Active                              
6          6     38728       7           Active                              
7          7     38728       8           Active                              
1          1     38728       9           Dev Complete                        
1          1     38728       10          Resolved; Queued for Build to Test  
2          2     38728       11          Resolved; Queued for Build to Test  
3          3     38728       12          Resolved; Queued for Build to Test 
1          8     38728       13          Active                             
2          9     38728       14          Active                             
1          2     38728       15          Dev Complete

这可以通过调整语法来完成吗？

Answer 1

使用 ROW_NUMBERS 的差异:

WITH CteGrp AS(
    SELECT *,
        Grp = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Rev)
                - ROW_NUMBER() OVER(PARTITION BY ID, State ORDER BY Rev)
    FROM tbl
),
Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY ID, Grp ORDER BY Rev)
    FROM CteGrp
)
SELECT
    rn, ID, Rev, State 
FROM Cte
ORDER BY ID, Rev

DEMO

---

阅读 Jeff Moden 的文章以获取有关此技术的更多信息:

Group Islands of Contiguous Dates (SQL Spackle) - SQLServerCentral