给一组行一个数值，并为下一组增加一个数值

Question

我用这个脚本创建了一个表：

CREATE TABLE [dbo].[BatchTest](
[Col1] [varchar](50) NULL,
[Col2] [varchar](50) NULL,
[Col3] [varchar](50) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NOT NULL,
CONSTRAINT [PK_BatchTest] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

我一直想知道CTE是否可以用来实现这一点：对于第一组记录，将BatchId列的值设置为固定数字，然后对于下一组记录，BatchId的值应为：prev。组的编号+1..依此类推

按条款分组：Col1、Col2、Col3

结果：

A   B   C   34
A   B   C   34
A   B   C   34
A   B   C   34
A   B   D   35
A   B   D   35
A   B   D   35
A   B   D   35
A   B   E   36
A   B   E   36
A   B   E   36
A   B   E   36

在这种情况下，启动34号。依此类推，为下一组记录递增它。开始编号由用户输入。

提前感谢！

Answer 1

我真的不确定我是否理解购买这个问题我想你在找dense_rank:

样本数据：

INSERT INTO BatchTest (Col1, Col2, Col3, BatchId) VALUES
('1', '1', '1', 0),
('1', '1', '2', 0),
('1', '1', '3', 0),
('1', '1', '1', 0),
('1', '1', '2', 0),
('1', '1', '3', 0);

查询：

SELECT Col1, Col2, Col3, BatchId, Id, DENSE_RANK() OVER(ORDER BY Col1, Col2, Col3) As DR
FROM BatchTest
ORDER BY DR

结果：

Col1    Col2    Col3    BatchId Id  DR
1       1       1       0       1   1
1       1       1       0       4   1
1       1       2       0       5   2
1       1       2       0       2   2
1       1       3       0       3   3
1       1       3       0       6   3

Answer 2

如果起始BatchId由用户指定，则可以使用变量修改DENSE_RANK()函数的值。使用您的数据集，稍微随机化以显示DENSE_RANK()正在工作，我插入了以下内容：

INSERT INTO BatchTest
(
Col1
,Col2
,Col3
,BatchId
)
VALUES 
('A','B','C',0),
('A','B','C',0),
('A','B','E',0),
('A','B','C',0),
('A','B','D',0),
('A','B','D',0),
('A','B','D',0),
('A','B','C',0),
('A','B','E',0),
('A','B','E',0),
('A','B','D',0),
('A','B','E',0)

然后，您可以使用一个变量作为BatchId的起始值，并将Zohar的最终查询修改为UPDATE，就像这样，使用CTE生成BatchId值，然后JOIN将CTE转换为BatchTest:

DECLARE @BatchId INT = 34
;WITH BatchedIds AS
(
SELECT 
ID
, Col1
, Col2
, Col3
, BatchId = ( DENSE_RANK() OVER ( ORDER BY Col1, Col2, Col3 )) + @BatchId - 1
FROM BatchTest
)
UPDATE bt
SET bt.BatchId = bi.BatchId
FROM BatchTest bt
INNER JOIN BatchedIds bi ON bi.ID = bt.ID

然后您可以查询BatchTest并获得以下结果：

SELECT * FROM BatchTest
ORDER BY BatchId
ID  Col1    Col2    Col3    BatchId
4   A       B       C       34
1   A       B       C       34
2   A       B       C       34
8   A       B       C       34
5   A       B       D       35
6   A       B       D       35
7   A       B       D       35
11  A       B       D       35
12  A       B       E       36
9   A       B       E       36
10  A       B       E       36
3   A       B       E       36