为行的GroupID列赋值
为行的GroupID列赋值
提问于 2019-06-26 03:55:38
下面的代码在下面的屏幕截图中生成行。目标是添加一个名为GroupID的新列，该列分配屏幕截图右侧以蓝色显示的值。
select
*
into #data
from
(
values
(157, 84152, 'termination', '7/31/2017'),
(157, 3025126, 'effective', '8/1/2017'),
(157, 3025126, 'termination', '8/31/2018'),
(157, 157, 'effective', '9/1/2018'),
(1176, 30, 'termination', '5/6/2017'),
(1176, 1176, 'effective', '5/7/2017'),
(1176, 1176, 'termination', '11/3/2017'),
(1176, 30, 'effective', '11/4/2017'),
(1176, 30, 'termination', '5/6/2018'),
(1176, 1176, 'effective', '5/7/2018'),
(1176, 1176, 'termination', '11/9/2018'),
(1176, 30, 'effective', '11/10/2018'),
(1176, 30, 'termination', '5/3/2019'),
(1176, 1176, 'effective', '5/4/2019')
) d (CurrentProducerID, FormerProducerID, DateType, DateValue);
alter table #data alter column DateValue date;
select * from #data;
我试过使用row_number()，但它没有给出想要的结果。这是我的尝试和结果：
select
*,
--GroupID = row_number() over(partition by CurrentProducerID order by DateValue)
GroupID = row_number() over(partition by CurrentProducerID, FormerProducerID order by DateValue)
from #data;
回答 1
Stack Overflow用户
发布于 2019-06-26 04:40:48
一种解决方案可以是：-
;with ccc as (
select *,ROW_NUMBER() over (order by (select 1)) id from #data
),cte as (
select t1.*,t2.id [t2id] from ccc t1
left outer join ccc t2 on t2.CurrentProducerID=t1.CurrentProducerID
and t2.FormerProducerID=t1.FormerProducerID
and (t2.DateType='termination' and t1.DateType='effective')
and (t1.id+1)=t2.id
),cte2 as (
select cte.id,ROW_NUMBER() over (Partition by CurrentProducerID order by id) [g] from cte where t2id is null
)
select cte.CurrentProducerID , cte.FormerProducerID , cte.DateType , cte.DateValue,isnull(cte2.g,cte3.g) g from cte
left join cte2 on cte.id=cte2.id
left join cte2 cte3 on cte.id+1=cte3.id结果如下：
CurrentProducerID FormerProducerID DateType DateValue g
157 84152 termination 2017-07-31 1
157 3025126 effective 2017-08-01 2
157 3025126 termination 2018-08-31 2
157 157 effective 2018-09-01 3
1176 30 termination 2017-05-06 1
1176 1176 effective 2017-05-07 2
1176 1176 termination 2017-11-03 2
1176 30 effective 2017-11-04 3
1176 30 termination 2018-05-06 3
1176 1176 effective 2018-05-07 4
1176 1176 termination 2018-11-09 4
1176 30 effective 2018-11-10 5
1176 30 termination 2019-05-03 5
1176 1176 effective 2019-05-04 6页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56761170
复制相关文章
相似问题
腾讯云开发者
