Oracle基于a、b、c分组生成递增rn序号的SELECT查询实现
Oracle 按首次出现顺序为a/b/c组合分配连续分组编号的实现方案
核心需求拆解
需要实现的效果是:
- 按照表中行的原生出现顺序,为首次出现的(a,b,c)组合按先后顺序分配连续递增的整数值
- 完全相同的(a,b,c)组合对应的所有行,共享同一个rn编号
- 编号顺序和组合的数值大小无关,只和组合第一次出现的行位置有关
可落地SQL实现
SELECT a, b, c, DENSE_RANK() OVER (ORDER BY first_occur_rn) AS rn FROM ( SELECT base_src.*, MIN(row_seq) OVER (PARTITION BY a, b, c) AS first_occur_rn FROM ( -- 锚定表原生扫描顺序,为每一行生成固定行号 SELECT t.*, ROWNUM AS row_seq FROM 实际业务表名 t -- 替换为你的实际表名 ) base_src );
实现逻辑说明
- 三层查询结构的设计完全规避了顺序错乱的问题:
- 最底层子查询:不添加任何排序、过滤逻辑,直接按Oracle表扫描的原生顺序为每一行生成
row_seq(即ROWNUM值),将题目要求的“行出现顺序”转化为可计算的固定数值,避免后续操作打乱原始行位置 - 中间层子查询:对每一行按(a,b,c)做窗口分区,计算同分区内最小的
row_seq值——这个值就是该(a,b,c)组合第一次出现时的行位置,作为组的唯一锚点 - 最外层查询:对组锚点值做稠密排序(DENSE_RANK),由于锚点值本身是按组合首次出现的先后顺序从小到大排布的,排序后得到的连续值正好匹配需求的递增规则;同一组合的所有行锚点值完全一致,因此会得到相同的rn值。
- 最底层子查询:不添加任何排序、过滤逻辑,直接按Oracle表扫描的原生顺序为每一行生成
常见避坑提示
- 禁止直接使用
DENSE_RANK() OVER (ORDER BY a,b,c)的写法:该逻辑是按a、b、c的数值大小排序分配编号,和“按行出现顺序”的要求完全不匹配,示例中数值更小的a=1类组合会被排到编号前列,和预期结果偏差极大。 - 禁止将ROWNUM计算放在带分组、排序的外层查询中:如果在后续做了分组、排序操作后再计算ROWNUM,会因为Oracle优化器的执行计划打乱原始行的顺序,导致行号和实际出现顺序不匹配,必须在最底层无逻辑过滤的子查询中先生成固定的
row_seq值。
内容的提问来源于stack exchange,提问作者user3468000
相关产品推荐
相关产品推荐

