在MS Access中查找重复行并递增S/N值的实现方案

需求场景 #

针对包含Name、S/N、id字段的数据表，需查找Name与S/N组合重复的行，将重复组内除第一条外的行的S/N值按id顺序依次递增1，保持前导零格式。

原表数据：

Name    S/N     id      
A       004      1
B       005      2
C       007      3
C       007      4
C       007      5
D       004      6

处理后目标数据：

Name    S/N     id      
A       004      1
B       005      2
C       007      3
C       008      4
C       009      5
D       004      6

实现步骤 #

1. 创建选择查询识别重复项与增量值 #

先通过子查询计算每一行在对应重复组中的增量值（重复组第一条增量为0，后续每条递增1）。假设表名为YourTable，SQL语句如下：

SELECT 
    t.id,
    t.Name,
    t.[S/N],
    (SELECT Count(*) 
     FROM YourTable AS t2 
     WHERE t2.Name = t.Name 
       AND t2.[S/N] = t.[S/N] 
       AND t2.id < t.id) AS IncrementValue
FROM YourTable AS t
ORDER BY t.Name, t.[S/N], t.id;

执行该查询可验证增量值是否正确，确保重复组内的增量符合预期。

2. 创建更新查询批量修改S/N值 #

基于上述查询的增量结果，编写更新查询实现S/N的递增更新。注意处理S/N的字符串格式（保留3位前导零）：

UPDATE YourTable AS t
INNER JOIN (
    SELECT 
        id,
        (SELECT Count(*) 
         FROM YourTable AS t2 
         WHERE t2.Name = t.Name 
           AND t2.[S/N] = t.[S/N] 
           AND t2.id < t.id) AS IncrementValue
    FROM YourTable
) AS sub ON t.id = sub.id
SET t.[S/N] = Format(Val(t.[S/N]) + sub.IncrementValue, "000");

关键逻辑说明：

• Val(t.[S/N])：将带前导零的字符串S/N转换为数值类型，便于计算增量
• + sub.IncrementValue：为重复行加上对应的增量值
• Format(..., "000")：将计算后的数值转回3位带前导零的字符串，保持格式统一

注意事项 #

• 备份数据：执行更新查询前务必备份原数据表，避免数据丢失
• 格式适配：如果你的S/N是其他位数（如4位），只需将Format函数中的"000"改为对应位数（如"0000"）即可

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