如何计算顺序列，并在sql中的行之前计算每一列

I am using MSSQL for my application and I need to count sequential number status predecessor line.

我正在为我的应用程序使用MSSQL,我需要计算序列号状态的前一行。

Table is like this,

表是这样的,

+------+------+
|Number|Status|
|     1|     N|
|     2|     G|
|     3|     G|
|     4|     N|
|     5|     N|
|     6|     G|
|     7|     G|
|     8|     N|
+------+------+

I suppose result set as follows ex.

我想结果集如下。

+------+------+-----+
|Number|Status|Count|
|     1|     N| 1   |
|     2|     G| 1   |
|     3|     G| 2   |
|     4|     N| 1   |
|     5|     N| 2   |
|     6|     G| 1   |   
|     7|     G| 2   |   
|     8|     G| 3   |
+------+------+-----+

I couldn't use cursor for performance of query. it is worst case options....

我无法使用游标来执行查询。这是最糟糕的选择....

2 个解决方案

#1

You need to identify groups of consecutive "N" and "G" values. I like to approach this with a difference of row numbers. Then you can use row_number() to enumerate the rows:

您需要识别连续的“N”和“G”值组。我喜欢用行数来区分这个。然后你可以使用row_number()来枚举行:

select t.number, t.status,
       row_number() over (partition by status, grp order by number) as seqnum
from (select t.*,
             (row_number() over (order by number) -
              row_number() over (partition by status order by number
             ) as grp
      from table t
     ) t;

#2

This is what you are looking for.

这就是你要找的。

https://msdn.microsoft.com/en-us/library/ms176102.aspx

#1