时间戳

首页 > 解决方案 > 使用序列根据条件增加计数器

sql-server - 使用序列根据条件增加计数器

问题描述

我正在尝试根据某些条件自动增加 SELECT 查询的计数器。

当 BatchID 和 Reference 值更改时，应该有一个计数器增加 1。

在 BatchID 保持不变的情况下，它应该只使用 2 个潜在的唯一编号，一个用于有参考值的地方，另一个用于没有参考值的地方。我在下面的示例数据中添加了一列，其中包含 ExpectedResultCounter，它显示了我期望的结果。

我已成功使用以下查询进行编号：

select 
    NEXT VALUE FOR seqAutoNumber OVER (ORDER BY RowFilter) AS ID,
    *
from
(
    select
        DENSE_RANK() OVER (ORDER BY BatchID, ContainsValue) AS RowFilter,
        ExpectedResultCounter,
        BatchID,
        Reference
    from
    (
        select CASE WHEN Reference is null then 1 else 0 END as ContainsValue, * 
        from   #Temp
    ) a
) 
b
order by 
    BatchID, 
    Reference

但是我无法让序列号与与 RowFilter 相同的编号匹配，因为它在给定的情况下坚持相同的编号。如果我尝试 PARTITION BY，则会收到以下错误：

Msg 11716, Level 15, State 1, Line 41
NEXT VALUE FOR function does not support the PARTITION BY clause.

有人在这里有什么想法吗？甚至可以使用序列来重复使用相同的数字吗？如果不是，那么在每次运行查询时都需要生成一组新的数字来解决这个问题的好方法是什么，而不是重新使用先前执行中的先前数字。

CREATE SEQUENCE [dbo].[seqAutoNumber] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 999999
 CYCLE 
 CACHE 
GO

Create Table #Temp
(
    Reference varchar(50) NULL,
    BatchID int,
    ExpectedResultCounter int
)

insert into #Temp
(
    Reference,
    BatchID,
    ExpectedResultCounter
)
SELECT 'P044276-8',21416,1 UNION ALL
SELECT 'E3723492-6',21419,2 UNION ALL
SELECT 'A62723432-1',21419,2 UNION ALL
SELECT 'P0402343250-4',21419,2 UNION ALL
SELECT 'P2602348-4',21419,2 UNION ALL
SELECT 'B0110662-2',21419,2 UNION ALL
SELECT 'P3234977-7',21419,2 UNION ALL
SELECT NULL,21419,3 UNION ALL
SELECT NULL,21419,3 UNION ALL
SELECT 'P382342391-1',21419,2 UNION ALL
SELECT NULL,21419,3 UNION ALL
SELECT 'Q234234234-3',21419,2 UNION ALL
SELECT 'E37234234-6',21468,4 UNION ALL
SELECT 'A6232432-1',21468,4 UNION ALL
SELECT 'P04023423450-4',21468,4 UNION ALL
SELECT 'P2623432408-4',21468,4 UNION ALL
SELECT 'B0023423462-2',21468,4 UNION ALL
SELECT NULL,21468,5 UNION ALL
SELECT NULL,21468,5 UNION ALL
SELECT NULL,21468,5 UNION ALL
SELECT NULL,21468,5 UNION ALL
SELECT NULL,21468,5

select * from #Temp
order by ExpectedResultCounter

drop table #Temp

标签： sql-servertsql

解决方案

你不能重复使用一个SEQUENCE号码。并且使用它有很多限制和限制。您可以参考NEXT VALUE FOR (Transact-SQL)

满足您要求的一种解决方法是将结果放入临时表中。

然后根据 BatchID 分组生成 ID 并更新回临时表

update  t
set     ResultCounter = r.ID
from
(
      select   BatchID, ID = NEXT VALUE FOR seqAutoNumber OVER (ORDER BY BatchID) 
      from     #Temp
      group by BatchID
) r
inner join #Temp t  on  r.BatchID   = t.BatchID

编辑 1：

update  t
set     ResultCounter = r.ID
from
(
    select  BatchID, 
            RefIsNull = CASE WHEN Reference IS NULL THEN 1 ELSE 0 END, 
            ID = NEXT VALUE FOR seqAutoNumber OVER (ORDER BY BatchID) 
    from    #Temp
    group by BatchID, CASE WHEN Reference IS NULL THEN 1 ELSE 0 END
) r
inner join #Temp t  on  r.BatchID   = t.BatchID
                    and (
                            (r.RefIsNull    = 1 and t.Reference is null)
                        or  (r.RefIsNull    = 0 and t.Reference is not null)
                        )

推荐阅读