比较当前行与 SQL Server 中的上一行 [英] Compare Current Row with Previous row in SQL Server

问题描述

我有一个名为 team 的表，如下所示:我刚刚在第三列中添加了一个 row_number

I have a table named team and it like below: I just added a row_number in the 3rd column

RaidNo  OutComeID   RN
2           15      1
4           15      2
6           14      3
8           16      4
10          16      5
12          14      6
14          16      7
16          15      8
18          15      9
20          16      10
22          12      11
24          16      12
26          16      13
28          16      14
30          15      15
32          14      16
34          13      17

当 OutcomeId 为 16 时，则从 1 开始，16 连续出现时，一一添加.结果就像

When the OutcomeId came as 16 then start with one and 16 comes consecutively, add one by one. And the results be like

RaidNo  OutComeID   RN  Result
2           15      1   0
4           15      2   0
6           14      3   0
8           16      4   1
10          16      5   2
12          14      6   0
14          16      7   1
16          15      8   0
18          15      9   0
20          16      10  1
22          12      11  0
24          16      12  1
26          16      13  2
28          16      14  3
30          15      15  0
32          14      16  0
34          13      17  0

帮助我得到结果.

推荐答案

您可以使用以下查询:

SELECT RaidNo, OutComeID, RN,
       CASE 
          WHEN OutComeID <> 16 THEN 0
          ELSE ROW_NUMBER() OVER (PARTITION BY OutComeID, grp ORDER BY RN) 
       END AS Result
FROM (
   SELECT RaidNo, OutComeID, RN,
          RN - ROW_NUMBER() OVER (PARTITION BY OutComeID ORDER BY RN) AS grp
   FROM mytable) AS t
ORDER BY RN

字段grp 标识具有相同OutComeID 值的连续记录的切片(也称为岛).外部查询使用 grp 来枚举属于 '16' 切片的每条记录.属于其他切片的记录被赋值0.

Field grp identifies slices (also called islands) of consecutive records having the same OutComeID value. The outer query uses grp in order to enumerate each record that belongs to a '16' slice. The records that belong to the other slices are assigned value 0.

此处演示

这篇关于比较当前行与 SQL Server 中的上一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！