代码之家 › 专栏 › 技术社区 › Anonymous

稠密秩,按A列划分,按B列的变化递增,按C列排序

gaps-and-islands ssms sql-server sql

1

Anonymous · 技术社区 · 7 年前

name|subtitle|date
ABC|excel|2018-07-07
ABC|excel|2018-08-08
ABC|ppt|2018-09-09
ABC|ppt|2018-10-10
ABC|excel|2018-11-11
ABC|ppt|2018-12-12
DEF|ppt|2018-12-31

我想添加一个列,每当副标题发生变化时,它就会递增,如下所示:

name|subtitle|date|Group_Number
ABC|excel|2018-07-07|1
ABC|excel|2018-08-08|1
ABC|ppt|2018-09-09|2
ABC|ppt|2018-10-10|2
ABC|excel|2018-11-11|3
ABC|ppt|2018-12-12|4
DEF|ppt|2018-12-31|1

有没有一个简单的方法来实现这一点?

1 回复 | 直到 7 年前

1

2

Panagiotis Kanavos 7 年前

快速回答

declare @table table (name varchar(20),subtitle varchar(20),[date] date )

insert into @table (name,subtitle,date)
values
('ABC','excel','2018-07-07'),
('ABC','excel','2018-08-08'),
('ABC','ppt','2018-09-09'),
('ABC','ppt','2018-10-10'),
('ABC','excel','2018-11-11'),
('ABC','ppt','2018-12-12'),
('DEF','ppt','2018-12-31');

with nums as (

    select *,  
         case when subtitle != lag(subtitle,1) over (partition by name order by date) 
              then 1 
              else 0 end as num
    from @table
)
select *,
    1+sum(num) over (partition by name order by date) AS Group_Number
from nums

解释

你问的不是排名。你在试着 detect "islands"

1 每次检测到更改时。

就是这样:

CASE WHEN subtitle != LAG(subtitle,1) OVER (PARTITION BY name ORDER BY date) 
     THEN 1

一旦你有了它,你就可以用一个运行总数来计算更改的数量:

sum(num) over (partition by name order by date) AS Group_Number

这将生成从0开始的值。要获取从1开始的数字,只需添加1:

1+sum(num) over (partition by name order by date) AS Group_Number

+1 :

with nums as (

    select *,  
         case when subtitle = lag(subtitle,1) over (partition by name order by date) 
              then 0 
              else 1 end as num
    from @table
)
select *,
    sum(num) over (partition by name order by date) AS Group_Number
from nums

更好的 检测孤岛的方法,即使这种情况下的结果是一样的。第一个查询将产生以下结果:

name    subtitle    date    num Group_Number
ABC     excel   2018-07-07  0   1
ABC     excel   2018-08-08  0   1
ABC     ppt     2018-09-09  1   2
ABC     ppt     2018-10-10  0   2
ABC     excel   2018-11-11  1   3
ABC     ppt     2018-12-12  1   4
DEF     ppt     2018-12-31  0   1

查询发出当检测到字幕中断时除了

第二个查询返回:

name    subtitle    date    num Group_Number
ABC     excel   2018-07-07  1   1
ABC     excel   2018-08-08  0   1
ABC     ppt     2018-09-09  1   2
ABC     ppt     2018-10-10  0   2
ABC     excel   2018-11-11  1   3
ABC     ppt     2018-12-12  1   4
DEF     ppt     2018-12-31  1   1

1