sql – 更改数字列

发布时间：2020-12-12 06:01:48 所属栏目：MsSql教程来源：网络整理

导读：我有NAME和PAY,但在这个例子中我需要CHANGEGROUP： NAME PAY DATE CHANGEGROUPSally 12 10/01/2011 1Sally 12 10/01/2011 1Sally 12 11/02/2011 1Sally 12 11/02/2011 1Sally 12 12/01/2012 1Sally 13 04/23/2013 2Sally 12 04/24/2013 3Sally 10 05/01/2013

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!