Transact-SQL - 直到满足条件的行数

Question

我试图生成“x”列中的数字，考虑到“eq”字段中的值。它应该为每个记录分配一个数字，直到遇到值“1”，下一行应重新开始计数。我尝试过使用row_number，但问题是我需要评估的列中只有1和0，并且我见过的使用row_number的情况都是在一个列中不断增长的值。我还尝试过使用rank，但我没有成功使其工作。

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

- MRamL

---

除非它对你的问题有帮助，否则你不应该编辑已经删除的内容。例如输入“早上好！”和“我真的很感激任何帮助”这样的内容会被看作是不好的行为。这只会增加额外的阅读量，从而减少了实际问题的关注度。 - CodyMR

谢谢建议。我进行了编辑，因为第一次我放了一张图片，但保存后没有显示出来。我猜测这是浏览器的问题。所以我把同样的内容改成了文本形式。 - MRamL

没问题：）。除非有人即将回答，否则我应该在一秒钟内为您提供答案。 - CodyMR

请告诉我我的回答对您是否有帮助。 - CodyMR

Answer 1

还有另一种解决方案可用：

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() over partition获取计数器。

这是一个使用Sql Server的示例

- EoinS

---

看起来不错。而且可以处理超过100行的数据。我会试一下的。谢谢！ - MRamL

你也可以更新x以获取组，然后从更新后的表中进行选择。这种方法更有意义，上述方法是一个“一站式解决方案”，如果你想要其他的更新/选择，请告诉我。 - EoinS

Answer 2

我这里有两个答案。一个基于ROW_NUMBER()，另一个基于似乎是你的索引(nInd)。我不确定你的索引中是否会有间隙，所以我也使用了ROW_NUMBER()。

我的表格式如下： myIndex int identity(1,1) NOT NULL number int NOT NULL 第一个是ROW_NUMBER()...

WITH rn AS (SELECT *, ROW_NUMBER() OVER (ORDER BY myIndex) AS rn, COUNT(*) AS max 
                  FROM counting c GROUP BY c.myIndex, c.number)
,cte (myIndex, number, level, row) AS (

    SELECT r.myIndex, r.number, 1, r.rn + 1 FROM rn r WHERE r.rn = 1
    UNION ALL
    SELECT r1.myIndex, r1.number, 
                       CASE WHEN r1.number = 0 AND r2.number = 1 THEN 1
                                                                 ELSE c.level + 1
                       END,
                       row + 1
    FROM cte c 
        JOIN rn r1 
            ON c.row = r1.rn
        JOIN rn r2
            ON c.row - 1 = r2.rn
    )

SELECT c.myIndex, c.number, c.level FROM cte c OPTION (MAXRECURSION 0);

现在索引...

WITH cte (myIndex, number, level) AS (

    SELECT c.myIndex + 1, c.number, 1 FROM counting c WHERE c.myIndex = 1
    UNION ALL
    SELECT c1.myIndex + 1, c1.number, 
                           CASE WHEN c1.number = 0 AND c2.number = 1    THEN 1
                                                                        ELSE c.level + 1
                           END
    FROM cte c 
        JOIN counting c1
            ON c.myIndex = c1.myIndex
        JOIN counting c2
            ON c.myIndex - 1 = c2.myIndex
    )

SELECT c.myIndex - 1 AS myIndex, c.number, c.level FROM cte c OPTION (MAXRECURSION 0);

- CodyMR

---

真漂亮！是的，我的索引是nInd，所以我会使用第二个方法，但两种方法都非常好用。非常感谢！ - MRamL

有趣。在某些情况下运行良好，但如果超过100行，则会出现以下错误：“语句完成之前已耗尽最大递归100次”。 - MRamL

@MRamL 对的。我忘了这一点，因为我从来没有在自己的查询中达到过最大值。在“from cte”之后加上“OPTION (MAXRECURSION 0)”。 - CodyMR

Answer 3

我现在的答案是通过使用

标签来实现的

。

Cursor

我知道如果没有使用游标的其他解决方案，对于性能方面来说会更好。

这是我的解决方案的快速演示：

  -- Create DBTest
  use master
  Go
  Create Database DBTest
  Go
  use DBTest
  GO
  -- Create table
  Create table Tabletest
  (nInd    int , eq  int)
  Go
  -- insert dummy data
  insert into Tabletest (nInd,eq) 
  values    (1,0),
            (2,1),
            (3,0),
            (4,1),
            (5,0),
            (6,0),
            (7,0),
            (8,0),
            (9,1),
            (8,0),
            (9,1)



  Create table #Tabletest (nInd int ,eq int ,x int )
  go

  DECLARE  @nInd int , @eq int , @x int
  set @x = 1
  DECLARE db_cursor CURSOR FOR  
  SELECT nInd , eq
  FROM Tabletest  
  order by nInd

  OPEN db_cursor   
  FETCH NEXT FROM db_cursor INTO @nInd , @eq   

  WHILE @@FETCH_STATUS = 0   
  BEGIN   

   if (@eq = 0) 
     begin

            insert into #Tabletest (nInd ,eq  ,x) values (@nInd , @eq , @x)
            set @x = @x +1
     end 
     else if (@eq = 1)
     begin
            insert into #Tabletest (nInd ,eq  ,x) values (@nInd , @eq , @x)
            set @x = 1
     end

    FETCH NEXT FROM db_cursor INTO @nInd , @eq   

  END   

  CLOSE db_cursor   
  DEALLOCATE db_cursor


  select * from #Tabletest

最终结果集将如下所示：

希望能有所帮助。

- ahmed abdelqader

---

谢谢。但是已经在存储过程中使用了游标来实现了。我正在尝试优化它。 :) - MRamL

Answer 4

从稍微不同的角度来看（这可能不是真的，但可以消除游标或递归CTE的需要），似乎你正在构建数据集内的有序分组。因此，首先找到这些组，然后确定每个组的排序。

真正的关键是确定查找正确分组的规则。根据你的描述和评论，我猜测分组是从开始（按nInd列排序）到每一行的eq值为1结束，所以你可以做如下操作：

;with ends(nInd, ord) as (
    --Find the ending row for each set
    SELECT nInd, row_number() over(order by nInd)
    FROM mytable
    WHERE eq=1
), ranges(sInd, eInd) as (
    --Find the previous ending row for each ending row, forming a range for the group
    SELECT coalesce(s.nInd,0), e.nInd
    FROM ends s
        right join ends e on s.ord=e.ord-1
)

然后，使用这些组范围，您可以找到每个的最终排序：

select t.nInd, t.Fecha, t.eq
    ,[x] = row_number() over(partition by sInd order by nInd)
from ranges r
    join mytable t on r.sInd < t.nInd
                    and t.nInd <= r.eInd
order by t.nInd

- chezy525