Oracle避免批量插入时分支内员工编号重复问题求助

你的问题出在批量插入时，所有同分支的行都会使用插入前的Max(Emp_number_1)+1值——因为查询是基于插入操作开始时的表快照计算的，所以同分支的多条数据会拿到同一个编号，导致重复。以下是不同数据库环境下的解决办法：

MySQL 解决方案 #

使用用户变量跟踪每个分支的当前编号，确保同分支内的行连续递增：

INSERT INTO emp_table_1 (Emp_Name_1, Emp_Branch_1, Emp_number_1)
SELECT 
    Emp_Name_2,
    Emp_Branch_2,
    @current_num := CASE 
        WHEN @prev_branch = Emp_Branch_2 THEN @current_num + 1
        ELSE (SELECT COALESCE(MAX(Emp_number_1), Emp_Branch_2) FROM emp_table_1 WHERE Branch_Cd = Emp_Branch_2) + 1
    END AS Emp_number_1
FROM emp_table_2
CROSS JOIN (SELECT @prev_branch := '', @current_num := 0) AS vars
ORDER BY Emp_Branch_2;

• COALESCE用于处理分支暂无员工的情况，默认从分支号开始递增
• 按分支排序保证变量能正确跟踪同分支的行

SQL Server 解决方案 #

利用窗口函数ROW_NUMBER()给每个分支内的行分配序号，加上分支当前最大编号：

WITH branch_max AS (
    SELECT Branch_Cd, COALESCE(MAX(Emp_number_1), Branch_Cd) AS max_num
    FROM emp_table_1
    GROUP BY Branch_Cd
)
INSERT INTO emp_table_1 (Emp_Name_1, Emp_Branch_1, Emp_number_1)
SELECT 
    t2.Emp_Name_2,
    t2.Emp_Branch_2,
    ISNULL(bm.max_num, t2.Emp_Branch_2) + ROW_NUMBER() OVER (PARTITION BY t2.Emp_Branch_2 ORDER BY t2.Emp_Name_2)
FROM emp_table_2 t2
LEFT JOIN branch_max bm ON t2.Emp_Branch_2 = bm.Branch_Cd;

• PARTITION BY Emp_Branch_2按分支分组生成序号
• ORDER BY可以根据实际需求调整（比如员工姓名、入职时间等）

Oracle 解决方案 #

同样使用窗口函数实现连续编号：

INSERT INTO emp_table_1 (Emp_Name_1, Emp_Branch_1, Emp_number_1)
SELECT 
    t2.Emp_Name_2,
    t2.Emp_Branch_2,
    NVL(bm.max_num, t2.Emp_Branch_2) + ROW_NUMBER() OVER (PARTITION BY t2.Emp_Branch_2 ORDER BY t2.Emp_Name_2)
FROM emp_table_2 t2
LEFT JOIN (
    SELECT Branch_Cd, MAX(Emp_number_1) AS max_num
    FROM emp_table_1
    GROUP BY Branch_Cd
) bm ON t2.Emp_Branch_2 = bm.Branch_Cd;

• 如果需要长期维护分支编号的连续性，也可以为每个分支创建独立的序列，结合触发器自动生成编号

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