sql-server - 重新编号连续值

Question

我有一张表，里面有比赛日期和结果(0 表示输，1 表示赢)

+------+--------------+--------+ 
| id   | game_date    | result | 
+------+--------------+--------+
| 1    | '2016-09-01' | 1      |
| 2    | '2016-09-02' | 1      |
| 3    | '2016-09-03' | 0      |
| 4    | '2016-09-04' | 1      |
| 5    | '2016-09-04' | 1      |
| 6    | '2016-09-04' | 1      |
| 7    | '2016-09-05' | 1      |
| 8    | '2016-09-06' | 0      |
| 9    | '2016-09-07' | 1      |

我需要获得所有胜利，其中胜利是连续的(按日期排序)并从 1 到最后一次胜利重新编号。

结果应该是这样的:

+------+--------------+--------+------------
| id   | game_date    | result | 
+------+--------------+--------+-------------
| 1    | '2016-09-01' | 1      | 1
| 2    | '2016-09-02' | 1      | 2
| 3    | '2016-09-03' | 0      |
| 4    | '2016-09-04' | 1      | 1
| 5    | '2016-09-04' | 1      | 2
| 6    | '2016-09-04' | 1      | 3
| 7    | '2016-09-05' | 1      | 4
| 8    | '2016-09-06' | 0      |
| 9    | '2016-09-07' | 1      | 1  

Answer 1

您可以通过识别相邻值的组来做到这一点。一种简单的方法是行号的差异。另一种方法是为每个“0”值分配最大日期:

select id, game_date, result,
       (case when result = 1
             then row_number() over (partition by result, max_gamedate order by id)
        end) as newcol
from (select t.*,
             max(case when result = 0 then game_date end) over
                 (order by id) as max_gamedate
      from t
     ) t