sql - 使用 row_number 添加组 ID 列

Question

请参阅下面的 DDL:

create table #Test (reference int identity,id int,dataset varchar(10),primary key (reference))
insert into #Test (id,dataset) values (1,'System1')
insert into #Test (id,dataset) values (2,'System10')
insert into #Test (id,dataset) values (9,'System3')
insert into #Test (id,dataset) values (0,'')
insert into #Test (id,dataset) values (8,'System6')
insert into #Test (id,dataset) values (19,'System7')
insert into #Test (id,dataset) values (0,'')
insert into #Test (id,dataset) values (55,'System10')
insert into #Test (id,dataset) values (62,'System4')

我正在寻找这样的输出:

ID 为零分隔组。到目前为止我已经尝试过:

select * from (
select  row_number() over (order by reference) as groupid, #test.* from #test

但是，当 ID 列中的值为零时，groupid 不会重置。

我意识到这是糟糕的设计。该表是由外部公司创建的。我只是查询而已。

Answer 1

试试这个:

SELECT *,
       COALESCE(SUM(CASE WHEN id = 0 THEN 1 END) 
                OVER (ORDER BY reference),0) + 1 AS GroupID
FROM #Test

该查询使用带有 ORDER BY 子句的 SUM OVER 来计算 0 值的运行总数。如果我们将 1 添加到此运行总计中，我们将获得所需的 GroupID 值。

注意:此版本的 SUM 从 SQL Server 2012 开始提供。

编辑:

您可以使用 OUTER APPLY 在旧版本的 SQL Server 中使用 ORDER BY 子句模拟 SUM OVER，如下面的查询所示:

SELECT t1.*,
       x.cnt + 1 AS GroupID
FROM #Test t1
OUTER APPLY (SELECT COUNT(cASE WHEN id = 0 THEN 1 END) AS cnt
             FROM #Test t2
             WHERE t2.reference <= t1.reference) AS x