更改号码栏 [英] Change number column

问题描述

我有NAME和PAY，但是在这个例子中我需要CHANGEGROUP：

I have NAME and PAY, but I need CHANGEGROUP in this example:

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（），但是它们按照这个值进行分组 - 因为工资下降了，它会扰乱我的分组。我看到这个，但它不是兼容这个SQL 2005的旧版本

I tried RANK() and DENSE_RANK(), but they group according to the value - because pay goes down, it messes up my grouping. I saw this but it's not compatible with this older version of SQL 2005

推荐答案

这是一个空白和岛屿问题。

This is a gaps and islands problem.

一种方法。 SQL小提琴

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 

这篇关于更改号码栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！