SQL Server 2019如何标识分区内所有行并处理最新行

我使用SQL Server 2019，需要处理每组中的最新行（ord值最大的行），同时标记同组内的其他行，方便知晓这些行在处理最新行时已被评估。实际场景更复杂，涉及多列且基于JSON列分区，这里用简化示例说明：

测试表与数据 #

创建测试表：

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [val] [nvarchar](50) NOT NULL,
    [ord] [int] NOT NULL
)

插入数据：

insert into test(val, ord)
values ('A', 1),
('A', 2),
('B', 1),
('B', 3)

数据如下：

现有查询与需求 #

我已经能用以下查询找出每组中ord最大的行（rownumber=1的行，即id=2、4）：

select *, ROW_NUMBER() over (partition by val ORDER BY ord DESC) as rownumber
from test

但还需要：

• 标记同分区的其他行（比如id=1和id=2同属A分区，id=3和id=4同属B分区）
• 生成ThePartition列，为每个分区分配唯一标识（无需连续整数）
• 最终要处理ord最大的行后，删除同分区内ord较小的行，且由于数据持续新增，不能在后续SQL中重复使用相同分区条件。

期望结果：

要生成ThePartition列并满足后续处理需求，可以使用DENSE_RANK()或哈希函数来生成唯一分区标识：

生成分区标识 #

方案1：连续整数分区标识 #

使用DENSE_RANK()按分区字段（示例中为val，实际场景替换为你的多列+JSON列条件）生成连续唯一标识：

SELECT 
    id,
    val,
    ord,
    ROW_NUMBER() OVER (PARTITION BY val ORDER BY ord DESC) AS rownumber,
    DENSE_RANK() OVER (ORDER BY val) AS ThePartition
FROM test
ORDER BY ThePartition, rownumber;

方案2：哈希值分区标识 #

如果不需要连续整数，可使用HASHBYTES()生成哈希值作为唯一标识（JSON列需先转为字符串）：

SELECT 
    id,
    val,
    ord,
    ROW_NUMBER() OVER (PARTITION BY val ORDER BY ord DESC) AS rownumber,
    CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', val), 2) AS ThePartition
FROM test
ORDER BY ThePartition, rownumber;

处理最新行并删除同分区其他行 #

利用CTE临时存储分区和行号信息，先处理最新行，再删除同组其他行：

WITH PartitionedData AS (
    SELECT 
        id,
        val,
        ord,
        ROW_NUMBER() OVER (PARTITION BY val ORDER BY ord DESC) AS rownumber
    FROM test
)
-- 处理每组的最新行（此处为示例查询，替换为你的实际处理逻辑）
SELECT * FROM PartitionedData WHERE rownumber = 1;

-- 删除同分区内非最新行
DELETE FROM test
WHERE id IN (SELECT id FROM PartitionedData WHERE rownumber > 1);

该逻辑每次执行都会重新计算分区和行号，无需重复编写分区条件，适配数据持续新增的场景。

内容的提问来源于stack exchange，提问作者Don Chambers