字段中特定值的连续编号

Question

我使用的是SQL Server 2017。

表:

CREATE TABLE [T1] 
(
REC_ID decimal(28,6) NOT NULL,
BUSINESS_NR decimal(10,6) NULL,
Description varchar(20) NULL,
);
INSERT INTO T1 (REC_ID,BUSINESS_NR, Description)
VALUES (312, 1, 'Created'),
(314, 1, 'Adjustment'),
(356, 2, 'Created'),
(388, 1, 'NoChange'),
(565, 2, 'Adjustment'),
(701, 2, 'NoChange'),
(769, 1, 'Adjustment'),
(832, 2, 'Adjustment'),
(992, 2, 'Closed'),
(995, 1, 'Closed');

问题:

我想给每个"调整"每个Business_NR一个连续的数字。

例子

SELECT * 
FROM T1 ....... 
ORDER BY Business_NR ASC, REC_ID ASC

输出:

+--------+-------------+-------------+-----------------+
| REC_ID | BUSINESS_NR | Description | Adjustment Count|
+--------+-------------+-------------+-----------------+
|    312 |           1 | Created     |                 |
|    314 |           1 | Adjustment  |        1        |
|    388 |           1 | NoChange    |                 |
|    769 |           1 | Adjustment  |        2        |
|    995 |           1 | Closed      |                 |
|    356 |           2 | Created     |                 |
|    565 |           2 | Adjustment  |        1        |
|    701 |           2 | NoChange    |                 |
|    832 |           2 | Adjustment  |        2        |
|    992 |           2 | Closed      |                 |
+--------+-------------+-------------+-----------------+

Info:每个表项的REC_ID是唯一且连续的。

我没有任何有用的尝试来展示，因此没有添加任何查询示例。

SQL FIDDLE: LINK

Answer 1

似乎你只是想要一个ROW_NUMBER在CASE表达式:

CASE [Description] WHEN 'Adjustment' THEN ROW_NUMBER() OVER (PARTITION BY BUSINESS_NR, [Description] ORDER BY REC_ID ASC) END AS AdjustmentCount