tSQL:每组加1

Question

尊敬的论坛和SQL专家：

是否可以通过SQL将每个组的更改加1？我已经尝试过Dense_Rank、Row_Number等agg函数了。但没有成功。

示例：

​

​

在这里你可以找到一些测试数据

​

DECLARE   @test  TABLE ([Session]  int, Sort int)
Insert into @test Values (1,1)
Insert into @test Values (1,2)
Insert into @test Values (1,3)
Insert into @test Values (0,4)
Insert into @test Values (0,5)
Insert into @test Values (1,6)
Insert into @test Values (1,7)
Insert into @test Values (1,8)
Insert into @test Values (1,9)
Insert into @test Values (1,10)
Insert into @test Values (1,11)
Insert into @test Values (1,12)
Insert into @test Values (1,13)
Insert into @test Values (0,14)
Insert into @test Values (1,15)
Insert into @test Values (0,16)
Insert into @test Values (1,17)
Insert into @test Values (1,18)
Insert into @test Values (1,19)
Insert into @test Values (1,20)
select * from @test

​

Answer 1

使用LAG()和SUM()窗口函数：

SELECT SUM(flag) OVER (ORDER BY Sort) GroupNumber, Session, Sort
FROM (
  SELECT *, CASE WHEN Session = LAG(Session) OVER (ORDER BY Sort) THEN 0 ELSE 1 END flag
  FROM @test
) t

请参阅demo。