如何在SQL UPDATE语句中按分组自动递增字段值

现有一张Employee表，EmpId为主键，TName字段值为空字符串，表数据如下：

需求：按GrId字段分组，为TName字段实现组内自动递增——同一GrId分组内从1开始计数，不同分组重新从1开始，且不能为TName字段使用Identity约束。

你尝试了以下SQL语句，但执行报错：

Update T
Set T.TName = '('+ (select CAST(Count(*) + 1 AS Nvarchar(max)) from Employee) + ')' 
from Employee T
Where 1=1 
group by T.GrId

期望输出结果：

正确的SQL实现方案 #

可以使用窗口函数ROW_NUMBER()来实现分组内的计数，以下是主流数据库的实现方式：

1. SQL Server 版本

WITH RankedEmployees AS (
    SELECT 
        EmpId,
        '(' + CAST(ROW_NUMBER() OVER (PARTITION BY GrId ORDER BY EmpId) AS NVARCHAR(MAX)) + ')' AS NewTName
    FROM Employee
)
UPDATE e
SET e.TName = re.NewTName
FROM Employee e
JOIN RankedEmployees re ON e.EmpId = re.EmpId;

2. MySQL 8.0+ 版本

WITH RankedEmployees AS (
    SELECT 
        EmpId,
        CONCAT('(', ROW_NUMBER() OVER (PARTITION BY GrId ORDER BY EmpId), ')') AS NewTName
    FROM Employee
)
UPDATE Employee e
JOIN RankedEmployees re ON e.EmpId = re.EmpId
SET e.TName = re.NewTName;

3. PostgreSQL 版本

WITH RankedEmployees AS (
    SELECT 
        EmpId,
        '(' || ROW_NUMBER() OVER (PARTITION BY GrId ORDER BY EmpId) || ')' AS NewTName
    FROM Employee
)
UPDATE Employee e
SET TName = re.NewTName
FROM RankedEmployees re
WHERE e.EmpId = re.EmpId;

逻辑说明

• PARTITION BY GrId：按GrId拆分分组，每个分组独立计算递增序号
• ORDER BY EmpId：基于主键EmpId排序，保证分组内的计数顺序稳定（主键唯一，不会出现排序冲突）
• 通过CTE（公共表表达式）生成带有序号的新TName值，再关联原表完成更新

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