SQL中如何实现按字段分组统一更新GRP_ID（替代Oracle自定义函数+序列写法）

原需求与数据场景 #

原表EXAMPLE的结构和样例数据如下（原表定义里COL2列名重复，实际业务字段为COL1、COL2、COL3、GRP_ID）：

核心需求是：给所有COL1、COL2、COL3取值完全一致的行，分配同一个连续不重复的组ID，更新替换原有的GRP_ID字段值。

原Oracle实现的缺陷：依靠自定义函数封装序列NEW_SEQUENCE的nextval取值逻辑生成组ID，在不允许创建自定义函数、无权限直接调用序列的当前SQL环境下完全无法运行。

通用零依赖实现方案 #

不需要自定义函数、不需要依赖序列权限，直接用SQL标准内置的DENSE_RANK()窗口函数就能实现完全等价的功能，兼容MySQL8.0+、PostgreSQL、SQL Server、Hive等绝大多数主流SQL环境。
实现逻辑和原Oracle代码的效果完全对齐：

• 对三个业务字段的组合做连续排名，同字段值的行拿到同一个排名值，也就是组ID
• DENSE_RANK()生成的连续不跳号整数，和原代码里逐次调用序列nextval生成的组ID效果完全一致

1. 提前校验分组结果 #

先执行查询确认生成的组ID匹配预期：

SELECT
  COL1,
  COL2,
  COL3,
  GRP_ID AS 原始组ID,
  -- 如需匹配样例里从100开始的组ID，在排名结果上加偏移即可：DENSE_RANK() OVER(...) + 99
  DENSE_RANK() OVER(ORDER BY COL1, COL2, COL3) AS 新组ID
FROM EXAMPLE;

执行后可以看到，所有三个字段完全相同的行，都会拿到同一个连续的新组ID，和原Oracle代码的输出没有功能差异。

2. 执行UPDATE更新原表组ID #

根据自己使用的SQL环境调整关联逻辑，核心是用行唯一标识（主键、PostgreSQL的CTID、SQL Server的物理行标识等）做关联，避免靠三个业务字段关联带来的全表扫描性能损耗：

UPDATE EXAMPLE AS T
SET GRP_ID = S.new_group_id
FROM (
  SELECT
    DENSE_RANK() OVER(ORDER BY COL1, COL2, COL3) AS new_group_id,
    -- 替换为你表自身的行唯一标识列
    CTID
  FROM EXAMPLE
) AS S
WHERE T.CTID = S.CTID;

注意事项 #

• 如果当前环境虽然不允许创建自定义函数，但仍有调用序列的权限，也可以直接在分组子查询里调用NEW_SEQUENCE.nextval，不需要额外封装函数，比如在GROUP BY的子查询里直接取序列值，效果和原代码一致
• 若不需要连续不跳号的组ID，也可以用ROW_NUMBER()取分组内的首行值关联更新，但DENSE_RANK()的实现代码更简洁，性能也更优

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