MySQL单库查询：如何按roleid均匀匹配参与者与评估者

核心逻辑 #

通过对参与者、评估者分别生成连续序号，再通过取整规则匹配序号，实现参与者按评估者人数均分：

1. 给所有参与者按自定义排序（默认按id升序）生成从0开始的连续序号
2. 给所有评估者按自定义排序（默认按id升序）生成从0开始的连续序号，同时统计评估者总人数
3. 计算每个评估者最少需要分配的参与者数量 = 向上取整(参与者总数 / 评估者总数)
4. 参与者序号除以单评估者分配数取整，结果匹配对应序号的评估者，即可完成均匀分配

前提假设 #

• 原始业务表名为 user_role
• 最终存储匹配关系的新表名为 participant_evaluator_map

MySQL 8.0+ 实现（支持CTE和窗口函数） #

步骤1：创建匹配结果表 #

CREATE TABLE IF NOT EXISTS participant_evaluator_map (
    id INT AUTO_INCREMENT PRIMARY KEY,
    participant_id INT NOT NULL COMMENT '参与者id',
    evaluator_id INT NOT NULL COMMENT '评估者id'
);

步骤2：执行匹配并插入数据 #

INSERT INTO participant_evaluator_map (participant_id, evaluator_id)
WITH 
participants AS (
    SELECT 
        id AS participant_id,
        ROW_NUMBER() OVER (ORDER BY id) - 1 AS p_seq
    FROM user_role
    WHERE roleid = 1
),
evaluators AS (
    SELECT 
        id AS evaluator_id,
        ROW_NUMBER() OVER (ORDER BY id) - 1 AS e_seq,
        COUNT(*) OVER () AS total_evaluator
    FROM user_role
    WHERE roleid = 2
),
p_count AS (
    SELECT COUNT(*) AS total_participant FROM participants
)
SELECT 
    p.participant_id,
    e.evaluator_id
FROM participants p
CROSS JOIN p_count pc
INNER JOIN evaluators e 
    ON e.e_seq = FLOOR(p.p_seq / CEIL(pc.total_participant / e.total_evaluator))
ORDER BY p.participant_id;

MySQL 5.x 兼容实现（使用变量替代窗口函数） #

步骤1：提前计算统计值存入变量 #

-- 统计评估者总人数
SELECT COUNT(*) INTO @total_e FROM user_role WHERE roleid = 2;
-- 统计参与者总人数
SELECT COUNT(*) INTO @total_p FROM user_role WHERE roleid = 1;
-- 计算单评估者最少分配参与者数
SET @per_e = CEIL(@total_p / @total_e);
-- 初始化参与者序号计数器
SET @p_seq = -1;
-- 初始化评估者序号计数器
SET @e_seq = -1;

步骤2：执行匹配并插入数据 #

INSERT INTO participant_evaluator_map (participant_id, evaluator_id)
SELECT p.pid, e.eid
FROM (
    SELECT id AS pid, @p_seq := @p_seq + 1 AS seq 
    FROM user_role 
    WHERE roleid = 1 
    ORDER BY id
) p
INNER JOIN (
    SELECT id AS eid, @e_seq := @e_seq + 1 AS seq 
    FROM user_role 
    WHERE roleid = 2 
    ORDER BY id
) e ON e.seq = FLOOR(p.seq / @per_e)
ORDER BY p.pid;

自定义调整说明 #

• 如果需要随机分配参与者，将参与者子查询中的ORDER BY id修改为ORDER BY RAND()即可
• 如果需要优先给特定评估者分配，调整评估者子查询中的排序规则即可
• 当参与者总数无法被评估者总数整除时，排序靠前的评估者会多分配1个参与者，符合均匀分配要求

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