如何在Mysql中为代理列表分配随机作业

Question

我想写一个脚本，允许我将随机作业分配给7个不同的代理。以下是我的2张表：

DROP TABLE IF EXISTS jobs;
CREATE TABLE `Jobs` (
`Job_id` SERIAL PRIMARY KEY,
`Start_Date` date DEFAULT NULL,
`End_Date` date DEFAULT NULL,
`Ref_no` int NOT NULL
) ;
INSERT INTO Jobs(Job_id,Start_Date, End_Date, Ref_no) VALUES
(1,'2018-09-01','2021-08-31',123456789),
(2,'2019-10-03','2020-10-02',987654321),
(3,'2020-11-01','2021-10-02',543210123),
(4,'2020-12-01','2022-11-30',481216181),
(5,'2018-04-01','2020-03-31',246810121),
(6,'2019-05-30','2020-05-29',369121518),
(7,'2019-11-01','2020-10-31',581114179);
DROP TABLE IF EXISTS agents;
CREATE TABLE `Agents` (
`Agent_id` SERIAL PRIMARY KEY,
`Agent_Name` varchar(255) NOT NULL UNIQUE
) ;
INSERT INTO Agents(Agent_id, Agent_Name) VALUES
(1,'Humpty'),
(2,'Mickey'),
(3,'Minnie'),
(4,'Daffy'),
(5,'Ellie'),
(6,'Jack'),
(7,'Jill');

现在我想做的是编写一个脚本，在7个代理之间随机分配作业。如果有人能建议我如何开始这件事，我将不胜感激。

提前谢谢。

Answer 1

在MySQL 8.x中，可以将RAND()和ROW_NUMBER()组合起来关联随机行。例如：

select *  
from (
select *, row_number() over(order by rand()) as rn
from `Jobs`
) j
join (
select *, row_number() over(order by rand()) as rn
from `Agents`
) a on j.rn = a.rn

请参阅DB Fiddle上的运行示例。

Answer 2

以下是8.0之前MySQL版本的方法…

SELECT x.*
, MOD(@i:=@i+1,7) + 1 i -- where '7' is the number of agents.
FROM
( SELECT j.*
FROM jobs j
ORDER
BY RAND()
) x
JOIN (SELECT @i := 0) vars
ORDER
BY i;

样本输出：

+--------+------------+------------+-----------+------+
| Job_id | Start_Date | End_Date   | Ref_no    | i    |
+--------+------------+------------+-----------+------+
|      5 | 2018-04-01 | 2020-03-31 | 246810121 |    1 |
|      3 | 2020-11-01 | 2021-10-02 | 543210123 |    2 |
|      1 | 2018-09-01 | 2021-08-31 | 123456789 |    3 |
|      7 | 2019-11-01 | 2020-10-31 | 581114179 |    4 |
|      4 | 2020-12-01 | 2022-11-30 | 481216181 |    5 |
|      6 | 2019-05-30 | 2020-05-29 | 369121518 |    6 |
|      2 | 2019-10-03 | 2020-10-02 | 987654321 |    7 |
+--------+------------+------------+-----------+------+