基于RRN为AS400 DB2的RECIPES文件按食谱重置填充序号

针对无键文件RECIPES的需求，我们可以利用DB2 for i的窗口函数和相对记录号（RRN）实现按食谱分组、从1开始递增填充Sequence No的效果。

第一步：验证编号逻辑 #

先执行查询确认生成的序号符合预期：

SELECT 
    "Recipe Number",
    "Ingredient number",
    "Ingredient Qty",
    ROW_NUMBER() OVER(PARTITION BY "Recipe Number" ORDER BY RRN(RECIPES)) AS "Sequence No"
FROM RECIPES;

这条语句会按Recipe Number分组，每组内依据RRN（原读取顺序）生成从1开始的连续序号，和你给出的输出示例完全匹配。

第二步：更新原表的Sequence No字段 #

如果验证结果正确，可选用以下两种方式更新原表：

方式一：MERGE语句（推荐，性能更优）

MERGE INTO RECIPES AS TGT
USING (
    SELECT 
        RRN(RECIPES) AS REC_RRN,
        ROW_NUMBER() OVER(PARTITION BY "Recipe Number" ORDER BY RRN(RECIPES)) AS NEW_SEQ
    FROM RECIPES
) AS SRC
ON RRN(TGT) = SRC.REC_RRN
WHEN MATCHED THEN
    UPDATE SET "Sequence No" = SRC.NEW_SEQ;

方式二：UPDATE子查询

UPDATE RECIPES
SET "Sequence No" = (
    SELECT ROW_NUMBER() OVER(PARTITION BY "Recipe Number" ORDER BY RRN(RECIPES))
    FROM RECIPES AS SUB
    WHERE RRN(SUB) = RRN(RECIPES)
);

关键逻辑说明 #

• RRN(RECIPES)：获取记录的相对记录号，确保排序顺序和按RRN读取时的原始顺序一致
• PARTITION BY "Recipe Number"：按食谱编号分组，每个食谱下的配料独立生成序号
• ROW_NUMBER()：在每个分组内生成从1开始的递增序号
• 两种更新方式均通过RRN作为唯一标识匹配记录，保证序号精准对应到原记录

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