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.

这是一个使用Sql Server的示例

这篇关于Transact-SQL-对行进行编号，直到满足条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！