我有NAME和PAY,但在这个例子中我需要CHANGEGROUP:
NAME PAY DATE CHANGEGROUP Sally 12 10/01/2011 1 Sally 12 10/01/2011 1 Sally 12 11/02/2011 1 Sally 12 11/02/2011 1 Sally 12 12/01/2012 1 Sally 13 04/23/2013 2 Sally 12 04/24/2013 3 Sally 10 05/01/2013 4 Sally 10 10/01/2014 4
我尝试了RANK()和DENSE_RANK(),但他们根据值进行分组 – 因为薪水下降,它会让我的分组变得混乱.我看到了this,但它与旧版本的sql 2005不兼容
解决方法
这是一个空白和岛屿问题.
一种方法. SQL Fiddle
WITH T1 AS (SELECT *,ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY DATE) - ROW_NUMBER() OVER ( PARTITION BY NAME,[PAY] ORDER BY DATE) AS Grp FROM Table1),T2 AS (SELECT *,MIN(DATE) OVER ( PARTITION BY NAME,Grp) AS MinDate FROM T1) SELECT [NAME],[PAY],[DATE],DENSE_RANK() OVER ( PARTITION BY NAME ORDER BY MinDate) AS CHANGEGROUP FROM T2 ORDER BY NAME,MinDate