SQL如何对表中的唯一receipt_id重新生成连续递增编号

不需要走先去重ID、重编号再关联回原表的流程，直接用DENSE_RANK()窗口函数就能一步实现，写法更简洁，执行效率也更高。

核心逻辑是不设置分区字段，仅按原始receipt_id排序做密集排名：相同原始receipt_id的行会得到同一个新编号，不同receipt_id的新编号连续递增，完全匹配你需要的输出效果。

参考SQL #

假设原表名为receipt_data，查询语句如下：

SELECT
  DENSE_RANK() OVER (ORDER BY receipt_id) AS receipt_id,
  item_id
FROM receipt_data
ORDER BY receipt_id, item_id;

之前用ROW_NUMBER()没得到预期结果的原因 #

• 直接使用ROW_NUMBER() OVER(ORDER BY receipt_id, item_id)时，函数会给每一行单独生成不重复的序号，同一个receipt_id下的不同item行会拿到不同编号，不符合分组同号的要求
• 加了PARTITION BY receipt_id再用ROW_NUMBER()时，序号会在每个receipt_id分组内从1重新开始计数，所有分组的首行序号都是1，无法生成全局连续的新ID

扩展适配 #

• 如果需要按收据实际产生的顺序编号，而非按receipt_id数值大小排序，且表中有记录创建时间字段（比如create_time），可以调整排序逻辑：

  SELECT
    DENSE_RANK() OVER (ORDER BY min_create_time, receipt_id) AS receipt_id,
    item_id
  FROM (
    SELECT 
      receipt_id,
      item_id,
      MIN(create_time) OVER (PARTITION BY receipt_id) AS min_create_time
    FROM receipt_data
  ) t
  ORDER BY receipt_id, item_id;
  

• 如果新编号需要从指定数值起始，直接在DENSE_RANK()返回结果上加对应偏移量即可，比如需要从100开始编号就写DENSE_RANK() OVER(ORDER BY receipt_id) + 99 AS receipt_id

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