Oracle如何生成SEQNO自增的未使用分支号缺失记录

原SQL问题原因 #

原写法所有NEWSEQ固定为1409无法自增，核心问题有3点：

• 聚合逻辑错误：分组字段携带了S.SEQNO，无法正确获取指定SOURCEID下的全局最大SEQNO作为起始基准
• 未做行号递增计算：所有匹配到的分支直接套用MAX(S.SEQNO)+1的固定值，没有给待新增的分支记录分配连续序号
• 关联逻辑有冗余：使用逗号做笛卡尔积+<>判断未使用分支，容易出现重复关联、结果重复的问题

正确实现SQL #

使用CTE拆分逻辑，配合窗口函数生成连续序号，代码如下：

WITH base_info AS (
    -- 取指定SOURCEID的基础参数：ID值、当前已使用的最大SEQNO
    SELECT 
        SOURCEID,
        MAX(SEQNO) AS max_seq
    FROM SOURCE 
    WHERE SOURCEID = '607'
    GROUP BY SOURCEID
),
unused_branch AS (
    -- 筛选所有未在该SOURCEID下使用的分支，生成从1开始的连续行号
    SELECT 
        b.BRANCH_NO,
        ROW_NUMBER() OVER(ORDER BY b.BRANCH_NO) AS row_num
    FROM BRANCH b
    WHERE NOT EXISTS (
        SELECT 1 
        FROM SOURCE s 
        WHERE s.SOURCEID = '607' 
          AND s.BRANCH = b.BRANCH_NO
    )
)
-- 拼接得到最终结果：最大SEQNO + 行号即为逐行递增的新SEQNO
SELECT 
    bi.SOURCEID,
    bi.max_seq + ub.row_num AS NEWSEQ,
    ub.BRANCH_NO
FROM base_info bi
CROSS JOIN unused_branch ub;

逻辑说明 #

• 第一步单独计算起始基准值，避免分组错误导致的SEQNO计算异常
• 用NOT EXISTS替代旧的不等值关联，准确筛选未使用的分支，不会产生冗余重复结果
• 用ROW_NUMBER()窗口函数给待新增的分支分配从1开始的连续序号，和最大SEQNO相加后，自然得到从1409开始、步长为1的递增NEWSEQ值
• 如果需要调整分支对应的SEQNO顺序，只需要修改ROW_NUMBER()里的ORDER BY规则即可
• 如果需要直接把生成的记录插入SOURCE表，直接在SELECT语句前加INSERT INTO SOURCE (SOURCEID, SEQNO, BRANCH)即可

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