我需要创建一个列,每次找到不同的值时,该列将从 1 开始计数。
这是我的表的示例:
if object_id('tempdb.dbo.#myTable') is not null drop table #myTable
create table #myTable (dates int, flag char)
insert into #myTable values('20181031','V')
insert into #myTable values('20181130','V')
insert into #myTable values('20181231','V')
insert into #myTable values('20190131','F')
insert into #myTable values('20190228','F')
insert into #myTable values('20190331','F')
insert into #myTable values('20190430','F')
insert into #myTable values('20190531','V')
insert into #myTable values('20190630','V')
insert into #myTable values('20190731','V')
select *
from #myTable
order by 1
dates flag
20181031 V
20181130 V
20181231 V
20190131 F
20190228 F
20190331 F
20190430 F
20190531 V
20190630 V
20190731 V
我需要输出如下所示:
dates flag cnt
20181031 V 1
20181130 V 2
20181231 V 3
20190131 F 1
20190228 F 2
20190331 F 3
20190430 F 4
20190531 V 1
20190630 V 2
20190731 V 3
我曾尝试使用row_number()
with 子查询的变体,但是当它遇到第二组 Vs 时,我无法让它再次开始计数。我查看了这两个解决方案,但我无法根据我的目的推断它:
计数行直到满足 where 子句条件
我正在使用 SQL Server 2012
谢谢
这是一种间隙和岛屿问题。最简单的解决方案是使用行号的差异来定义组。然后为您想要的列添加一个额外的行号:
select t.*,
row_number() over (partition by flag, seqnum - seqnum_2 order by date) as cnt
from (select t.*,
row_number() over (order by date) as seqnum,
row_number() over (partition by flag order by date) as seqnum_2
from #mytable t
) t
order by date;
这是一个 db<>fiddle。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句