请求：按指定字段分组为SQL重复记录添加序号

嗨Mary，我来帮你搞定这个重复记录分组编号的问题～

需求说明 #

你需要根据sCustLastName、sCustFirstName、sDealerNumber、sProductCode和sVIN这5个字段对重复记录分组，为每组内的记录依次生成连续序号（也就是你要的Count列）。

解决方案：使用SQL窗口函数ROW_NUMBER() #

在支持窗口函数的SQL数据库（比如SQL Server、MySQL 8.0+、PostgreSQL等）中，ROW_NUMBER()可以完美实现这个需求：它会按指定字段分组，再在每组内生成连续的序号。

核心查询代码

SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY sCustLastName, sCustFirstName, sDealerNumber, sProductCode, sVIN
        ORDER BY sVIN -- 同组记录都是重复的，这里可以替换成任意字段，不影响编号顺序
    ) AS Count,
    sCustLastName,
    sCustFirstName,
    sDealerNumber,
    sProductCode,
    sVIN
FROM Contract_Header

验证步骤（含测试数据） #

1. 创建测试表

Create Table Contract_Header (
 sCustLastName varchar(20),
 sCustFirstName varchar(20),
 sDealerNumber nvarchar(20),
 sProductCode varchar(3),
 sVIN nvarchar(20)
)

2. 插入测试数据

Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln1','Fn1','TWS00967','GAP','1HGCR2F5XHA151050')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln1','Fn1','TWS00967','GAP','1HGCR2F5XHA151050')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln1','Fn1','TWS00967','GAP','1HGCR2F5XHA151050')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln2','Fn2','TWS00968','GAP','WVWEV7AJXAW197040')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln2','Fn2','TWS00968','GAP','WVWEV7AJXAW197040')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln3','Fn3','TWS00967','GAP','2HGFC2F56GH536572')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln3','Fn3','TWS00967','GAP','2HGFC2F56GH536572')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln4','Fn4','TWS00967','PPM','19XFC2F76JE003513')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln4','Fn4','TWS00967','PPM','19XFC2F76JE003513')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln4','Fn4','TWS00967','PPM','19XFC2F76JE003513')

3. 执行查询后的结果

Count sCustLastName sCustFirstName sDealerNumber sProductCode sVIN
1 Ln1 Fn1 TWS00967 GAP 1HGCR2F5XHA151050
2 Ln1 Fn1 TWS00967 GAP 1HGCR2F5XHA151050
3 Ln1 Fn1 TWS00967 GAP 1HGCR2F5XHA151050
1 Ln2 Fn2 TWS00968 GAP WVWEV7AJXAW197040
2 Ln2 Fn2 TWS00968 GAP WVWEV7AJXAW197040
1 Ln3 Fn3 TWS00967 GAP 2HGFC2F56GH536572
2 Ln3 Fn3 TWS00967 GAP 2HGFC2F56GH536572
1 Ln4 Fn4 TWS00967 PPM 19XFC2F76JE003513
2 Ln4 Fn4 TWS00967 PPM 19XFC2F76JE003513
3 Ln4 Fn4 TWS00967 PPM 19XFC2F76JE003513

小提示 #

• PARTITION BY后面的字段是分组的核心依据，确保同一组的记录这些字段值完全一致；
• ORDER BY用来定义每组内的编号顺序，因为同组记录都是重复的，所以选任意字段都不会影响最终编号结果。

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