PostgreSQL中基于子串分组生成唯一ID更新表字段的方法

嘿，你的需求其实很好实现，核心是先给每个符合规则的分组生成唯一标识，再关联回原表更新。你的思路方向没错，但直接用GROUP BY没法直接对应到每一行数据，得调整下写法。下面分几种常用场景给你具体方案：

1. 使用窗口函数（适用于PostgreSQL、SQL Server、MySQL 8.0+） #

这种方法是目前最简洁高效的，利用DENSE_RANK()窗口函数，按照指定的分组规则生成连续的唯一ID，再关联更新原表。

步骤1：生成分组的唯一标识 #

先通过CTE（公共表表达式）给每条数据标记对应分组的ID：

WITH grouped_data AS (
    SELECT 
        id,
        DENSE_RANK() OVER (ORDER BY condition1, condition2, LEFT(condition3, 5)) AS new_target_id
    FROM table1
)

这里DENSE_RANK()会按照condition1、condition2和condition3前5个字符的组合排序，给每个不同的组合分配连续的唯一ID，正好匹配你预期的结果（1、1、2、3、3）。

步骤2：更新原表 #

把CTE和原表通过id关联，完成更新：

WITH grouped_data AS (
    SELECT 
        id,
        DENSE_RANK() OVER (ORDER BY condition1, condition2, LEFT(condition3, 5)) AS new_target_id
    FROM table1
)
UPDATE table1
SET target_id = gd.new_target_id
FROM grouped_data gd
WHERE table1.id = gd.id;

注意：MySQL的UPDATE语法略有不同，写法如下：

WITH grouped_data AS (
    SELECT 
        id,
        DENSE_RANK() OVER (ORDER BY condition1, condition2, LEFT(condition3, 5)) AS new_target_id
    FROM table1
)
UPDATE table1 t1
JOIN grouped_data gd ON t1.id = gd.id
SET t1.target_id = gd.new_target_id;

2. 兼容旧版本数据库（比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用临时表+变量的方式实现：

步骤1：创建临时表存储分组ID #

先提取所有唯一的分组组合，用自增变量给每个分组分配ID：

CREATE TEMPORARY TABLE group_ids AS
SELECT 
    condition1,
    condition2,
    LEFT(condition3, 5) AS condition3_prefix,
    @row := @row + 1 AS target_id
FROM (
    SELECT DISTINCT condition1, condition2, LEFT(condition3, 5)
    FROM table1
    ORDER BY condition1, condition2, LEFT(condition3, 5)
) AS unique_groups,
(SELECT @row := 0) AS init;

步骤2：关联临时表更新原表 #

通过分组条件把临时表和原表关联，更新target_id：

UPDATE table1 t1
JOIN group_ids gi 
    ON t1.condition1 = gi.condition1
    AND t1.condition2 = gi.condition2
    AND LEFT(t1.condition3, 5) = gi.condition3_prefix
SET t1.target_id = gi.target_id;

更新完成后可以删除临时表：DROP TEMPORARY TABLE group_ids;

验证结果 #

执行完更新后，查询原表：

SELECT * FROM table1 ORDER BY id;

会得到你预期的结果：

id, condition1, condition2, condition3, target_id
1, Westminster, Abbey Road, NW1 1FS, 1
2, Westminster, Abbey Road, NW1 1FG, 1
3, Westminster, China Road, NW1 1FG, 2
4, Wandsworth, China Road, SE5 3LG, 3
5, Wandsworth, China Road, SE5 3LS, 3

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