如何为相同tenantid的行自动分配统一格式的userid？

现有数据表：

需求：为所有相同tenantid的行分配统一的userid，格式为distinct_user_#（示例中简写为d_u_#）；由于tenantid是随机生成的，无法手动设置userid。期望效果：

核心思路是通过对tenantid分组排名，将排名与固定前缀拼接成目标userid。以下是主流数据库的具体实现：

MySQL 8.0+ / PostgreSQL / SQL Server #

查询生成结果（不修改原表） #

SELECT 
    CONCAT('d_u_', DENSE_RANK() OVER (ORDER BY tenantid)) AS userid,
    tenantid
FROM your_table_name;

若需要完整格式distinct_user_#，将CONCAT内的前缀替换为'distinct_user_'即可。

更新原表（直接修改数据） #

通过子查询获取每个tenantid对应的编号，再关联更新原表：

MySQL

UPDATE your_table_name t
JOIN (
    SELECT 
        tenantid,
        CONCAT('d_u_', DENSE_RANK() OVER (ORDER BY tenantid)) AS new_userid
    FROM (SELECT DISTINCT tenantid FROM your_table_name) AS distinct_tenants
) AS tenant_mapping
ON t.tenantid = tenant_mapping.tenantid
SET t.userid = tenant_mapping.new_userid;

PostgreSQL

WITH tenant_mapping AS (
    SELECT 
        tenantid,
        CONCAT('d_u_', DENSE_RANK() OVER (ORDER BY tenantid)) AS new_userid
    FROM (SELECT DISTINCT tenantid FROM your_table_name) AS distinct_tenants
)
UPDATE your_table_name t
SET userid = tm.new_userid
FROM tenant_mapping tm
WHERE t.tenantid = tm.tenantid;

SQL Server

WITH tenant_mapping AS (
    SELECT 
        tenantid,
        CONCAT('d_u_', DENSE_RANK() OVER (ORDER BY tenantid)) AS new_userid
    FROM (SELECT DISTINCT tenantid FROM your_table_name) AS distinct_tenants
)
UPDATE t
SET t.userid = tm.new_userid
FROM your_table_name t
JOIN tenant_mapping tm ON t.tenantid = tm.tenantid;

旧版MySQL（无窗口函数） #

针对MySQL 5.x版本，使用变量实现分组排名：

查询生成结果 #

SELECT 
    CONCAT('d_u_', @rank := IF(@prev_tenant = tenantid, @rank, @rank + 1)) AS userid,
    tenantid,
    @prev_tenant := tenantid
FROM your_table_name, (SELECT @rank := 0, @prev_tenant := '') AS vars
ORDER BY tenantid;

更新原表 #

UPDATE your_table_name t
JOIN (
    SELECT 
        tenantid,
        CONCAT('d_u_', @rank := IF(@prev_tenant = tenantid, @rank, @rank + 1)) AS new_userid,
        @prev_tenant := tenantid
    FROM (SELECT DISTINCT tenantid FROM your_table_name ORDER BY tenantid) AS distinct_tenants,
         (SELECT @rank := 0, @prev_tenant := '') AS vars
) AS tenant_mapping
ON t.tenantid = tenant_mapping.tenantid
SET t.userid = tenant_mapping.new_userid;

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