MySQL如何从两张表取数插入新表并按规则生成唯一roll_number

步骤1：创建目标表 table_out #

先创建符合字段、外键约束要求的新表：

CREATE TABLE table_out (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    Application_id INT NOT NULL COMMENT '关联table2的申请ID',
    can_name VARCHAR(64) NOT NULL COMMENT '考生姓名',
    venu_id INT NOT NULL COMMENT '关联table1的考点ID',
    roll_number VARCHAR(32) NOT NULL UNIQUE COMMENT '生成的准考号',
    -- 配置外键约束
    FOREIGN KEY (Application_id) REFERENCES table2(application_id),
    FOREIGN KEY (venu_id) REFERENCES table1(venu_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注：table2示例中重复出现的can_name字段属于笔误，可自行清理重复字段后执行操作。

步骤2：插入关联数据并生成指定格式的roll_number #

roll_number生成规则：按venu_code分组，组内按venu_address、venu_id、application_id排序生成连续序号，序号补零到7位后和venu_code拼接，最终得到类似290000001的格式。

适用MySQL 8.0及以上版本（支持窗口函数） #

INSERT INTO table_out (Application_id, can_name, venu_id, roll_number)
SELECT 
    t2.application_id,
    t2.can_name,
    t1.venu_id,
    -- 拼接venu_code和7位补零的组内序号
    CONCAT(
        t1.venu_code,
        LPAD(
            ROW_NUMBER() OVER (PARTITION BY t1.venu_code ORDER BY t1.venu_address, t1.venu_id, t2.application_id),
            7,
            '0'
        )
    ) AS roll_number
FROM table2 t2
-- 关联考点表，此处示例按考生申请ID升序依次分配到按地址排序的考点，每个考点最多容纳venu_capacity个考生
-- 如有其他考生-考点匹配规则，可自行修改JOIN逻辑
JOIN (
    SELECT 
        venu_id, venu_code, venu_capacity, venu_address,
        ROW_NUMBER() OVER (ORDER BY venu_address, venu_id) AS venue_order
    FROM table1
) t1 ON t1.venue_order = CEIL(ROW_NUMBER() OVER (ORDER BY t2.application_id) / t1.venu_capacity)
WHERE t2.fee_payment_status = 'success';

适用MySQL 5.x版本（不支持窗口函数，用用户变量实现） #

-- 初始化序号计算变量
SET @current_venu_code = '';
SET @group_row = 0;
SET @global_apply_row = 0;
SET @venue_order = 0;

INSERT INTO table_out (Application_id, can_name, venu_id, roll_number)
SELECT 
    application_id,
    can_name,
    venu_id,
    CONCAT(
        venu_code,
        LPAD(
            IF(@current_venu_code = venu_code, @group_row := @group_row + 1, @group_row := 1 AND @current_venu_code := venu_code),
            7,
            '0'
        )
    ) AS roll_number
FROM (
    SELECT 
        t2.application_id,
        t2.can_name,
        t1.venu_id,
        t1.venu_code
    FROM (
        SELECT application_id, can_name, @global_apply_row := @global_apply_row + 1 AS apply_row
        FROM table2
        WHERE fee_payment_status = 'success'
        ORDER BY application_id
    ) t2
    JOIN (
        SELECT 
            venu_id, venu_code, venu_capacity,
            @venue_order := @venue_order + 1 AS venue_order
        FROM table1
        ORDER BY venu_address, venu_id
    ) t1 ON t1.venue_order = CEIL(t2.apply_row / t1.venu_capacity)
    ORDER BY t1.venu_code, t1.venu_address, t1.venu_id, t2.application_id
) AS final_data;

注意事项 #

1. 插入数据前可将INSERT关键字替换为SELECT，先验证生成的roll_number、考生和考点的关联关系是否符合预期，确认无误再执行插入。
2. 如果需要调整序号的长度，修改LPAD函数的第二个参数即可，比如要生成8位序号就将7改为8。
3. 示例中的考生和考点匹配逻辑为按申请ID升序分配到按地址排序的考点，如果你有其他匹配规则，修改JOIN部分的关联条件即可。

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