Transact-SQL-对行进行编号,直到满足条件 [英] Transact-SQL - number rows until condition met
问题描述
我正在尝试在"x"中生成数字.列考虑字段"eq"中的值,以这种方式它应该为每个记录分配一个数字,直到它满足值"1"为止,并且下一行应该重置并再次开始计数.我已经尝试过使用 row_number
,但是问题是我需要评估的列中只有一个和零,而使用 row_number
的情况是在列中使用增长值.也尝试过等级,但是我没有设法使它起作用.
I'm trying to generate the numbers in the "x" column considering the values in field "eq", in a way that it should assign a number for every record until it meets the value "1", and the next row should reset and start counting again. I've tried with row_number
, but the problem is that I only have ones and zeros in the column I need to evaluate, and the cases I've seen using row_number
were using growing values in a column. Also tried with rank, but I haven't managed to make it work.
nInd Fecha Tipo @Inicio @contador_I @Final @contador_F eq x
1 18/03/2002 I 18/03/2002 1 null null 0 1
2 20/07/2002 F 18/03/2002 1 20/07/2002 1 1 2
3 19/08/2002 I 19/08/2002 2 20/07/2002 1 0 1
4 21/12/2002 F 19/08/2002 2 21/12/2002 2 1 2
5 17/03/2003 I 17/03/2003 3 21/12/2002 2 0 1
6 01/04/2003 I 17/03/2003 4 21/12/2002 2 0 2
7 07/04/2003 I 17/03/2003 5 21/12/2002 2 0 3
8 02/06/2003 F 17/03/2003 5 02/06/2003 3 0 4
9 31/07/2003 F 17/03/2003 5 31/07/2003 4 0 5
10 31/08/2003 F 17/03/2003 5 31/08/2003 5 1 6
11 01/09/2005 I 01/09/2005 6 31/08/2003 5 0 1
12 05/09/2005 I 01/09/2005 7 31/08/2003 5 0 2
13 31/12/2005 F 01/09/2005 7 31/12/2005 6 0 3
14 14/01/2006 F 01/09/2005 7 14/01/2006 7 1 4
推荐答案
还有另一种解决方案:
select
nind, eq, row_number() over (partition by s order by s)
from (
select
nind, eq, coalesce((
select sum(eq) +1 from mytable pre where pre.nInd < mytable.nInd)
,1) s --this is the sum of eq!
from mytable) g
内部子查询为 eq
中每次出现的 1
依次创建 groups
.然后,我们可以在分区上使用 row_number()
来获取计数器.
The inner subquery creates groups
sequentially for each occurrence of 1
in eq
. Then we can use row_number() over partition
to get our counter.
这篇关于Transact-SQL-对行进行编号,直到满足条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!