MySQL单表查询实现参评人与评估人跨部门公平配对的SQL实现

• 角色划分：roleid字段值为1是参评人，值为2是评估人
• 配对规则：
  • 同部门的评估人与参评人不得配对
  • 所有评估人分配到的参评人数量尽可能均匀

首先修正你原有建表、插入语句的语法错误（字符串字段值需加单引号）：

CREATE TABLE users (
  id INT,
  name VARCHAR(10),
  roleid INT,
  departement VARCHAR(10)
);

INSERT INTO users VALUES ( 1, 'a', 1, 'xx' );
INSERT INTO users VALUES ( 2, 'b', 1, 'yy' );
INSERT INTO users VALUES ( 3, 'c', 1, 'zz' );
INSERT INTO users VALUES ( 4, 'd', 1, 'xx' );
INSERT INTO users VALUES ( 5, 'e', 1, 'yy' );
INSERT INTO users VALUES ( 6, 'f', 2, 'xx' );
INSERT INTO users VALUES ( 7, 'g', 2, 'yy' );
INSERT INTO users VALUES ( 8, 'h', 2, 'zz' );

1. 直接用固定模运算匹配，没有考虑同部门过滤后会出现匹配丢失的情况
2. 没有动态统计评估人的已分配参评人数，无法满足均匀分配的要求

WITH participant AS (
    -- 提取所有参评人数据
    SELECT id AS participant_id, departement AS participant_departement
    FROM users WHERE roleid = 1
),
evaluator AS (
    -- 提取所有评估人数据
    SELECT id AS evaluator_id, departement AS evaluator_departement
    FROM users WHERE roleid = 2
),
valid_pair AS (
    -- 过滤出所有跨部门的合法配对候选
    SELECT 
        p.participant_id,
        p.participant_departement,
        e.evaluator_id,
        e.evaluator_departement
    FROM participant p
    CROSS JOIN evaluator e
    WHERE p.participant_departement <> e.evaluator_departement
),
rank_pair AS (
    -- 为每个参评人的合法评估人排序，优先选择当前分配数量少的评估人，保证分配均匀
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY participant_id 
            ORDER BY COUNT(*) OVER (PARTITION BY evaluator_id) ASC, evaluator_id ASC
        ) AS rn
    FROM valid_pair
)
-- 生成最终配对结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY participant_id) AS id,
    participant_id,
    evaluator_id,
    participant_departement,
    evaluator_departement
FROM rank_pair
WHERE rn = 1;

运行上述SQL可得到符合规则的均匀配对结果，如下是其中一种可能输出：

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