我有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