带特殊条件的邮箱地址随机配对MySQL实现问题

你的现有代码在奇数场景下存在两处问题：一是用lineCount / 2作为偏移量会因奇数产生小数，导致配对逻辑失效；二是最后三个用户被设置为向自己赠送，完全不符合循环配对的要求。以下是严格按照你需求实现的修正版本：

完整存储过程代码 #

CREATE TABLE IF NOT EXISTS CasoB(
    id INT PRIMARY KEY AUTO_INCREMENT,
    persons VARCHAR(50),
    personsToGive VARCHAR(50) 
);

INSERT INTO CasoB (persons, personsToGive) VALUES 
('a.tomas', NULL),
('g.rubio', NULL),
('a.pulido', NULL),
('m.fabrega.1', NULL),
('d.lazaro', NULL),
('c.albinya', NULL),
('b.gomez', NULL),
('j.espinoza', NULL),
('j.aguilera', NULL),
('j.da.silva.1', NULL),
('a.chamorro', NULL);

DELIMITER //

CREATE OR REPLACE PROCEDURE GenerateRandomOrderB()
BEGIN
    DECLARE lineCount INT;
    DECLARE evenPartCount INT;

    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS RandomOrderB;

    -- 创建随机排序的临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS RandomOrderB (
        id INT PRIMARY KEY,
        persons VARCHAR(50),
        RowNumber INT,
        TotalCount INT
    );

    SELECT COUNT(*) INTO lineCount FROM CasoB;
    SET evenPartCount = lineCount - 3;

    -- 插入随机排序的数据
    INSERT INTO RandomOrderB (id, persons, RowNumber, TotalCount)
    SELECT  id, 
            persons,
            ROW_NUMBER() OVER(ORDER BY RAND()) AS RowNumber,
            lineCount AS TotalCount
    FROM    CasoB;

    IF lineCount % 2 = 0 THEN
        -- 偶数场景：两两互赠
        UPDATE CasoB
        SET personsToGive = (
            SELECT r2.persons
            FROM RandomOrderB AS r1
            INNER JOIN RandomOrderB AS r2
            ON r2.RowNumber = r1.RowNumber + (lineCount / 2)
            WHERE r1.id = CasoB.id
        );
    ELSE
        -- 奇数场景：先处理前N-3个（偶数数量）
        UPDATE CasoB
        SET personsToGive = (
            SELECT r2.persons
            FROM RandomOrderB AS r1
            INNER JOIN RandomOrderB AS r2
            ON r2.RowNumber = r1.RowNumber + (evenPartCount / 2)
            WHERE r1.id = CasoB.id
            AND r1.RowNumber <= evenPartCount
        );

        -- 单独处理最后三个，形成循环配对（A→B，B→C，C→A）
        UPDATE CasoB cb
        JOIN (
            SELECT 
                persons,
                -- 用LEAD函数获取下一个的邮箱，最后一个取第一个的邮箱
                COALESCE(LEAD(persons) OVER(ORDER BY RowNumber), 
                         FIRST_VALUE(persons) OVER(ORDER BY RowNumber)) AS target_person
            FROM RandomOrderB
            WHERE RowNumber > evenPartCount
        ) last_three ON cb.persons = last_three.persons
        SET cb.personsToGive = last_three.target_person;
    END IF;
    
    DROP TEMPORARY TABLE IF EXISTS RandomOrderB;
END //

DELIMITER ;

关键逻辑说明 #

1. 偶数场景：保留你原本正确的逻辑，将随机排序后的列表分成前后两半，前半区的每个用户对应后半区的用户，实现双向互赠。
2. 奇数场景：
   • 先处理前lineCount-3个用户：这部分数量是偶数，用(lineCount-3)/2作为偏移量，和偶数场景逻辑一致，保证两两互赠。
   • 最后三个用户处理：通过LEAD()函数获取随机排序后下一个用户的邮箱，最后一个用户则取这三个中的第一个，形成完美的循环闭环（A→B，B→C，C→A）。

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