是否可以使用一组设置值随机更新行？

我有一个工作分配表，需要帮助。我想做的是将人的名字随机分配给表中的行。例如，该表当前如下所示：

        TASK     |NAME
  1 Get Chicken  |
  2 Clean Chicken|
  3 Cook Chicken |
  4 Eat Chicken  |
  5 Eat Corn     |
  6 Takeout Trash|

我有4名员工想要分配这些任务，但不想表现出任何偏爱。该表如下所示：

NAME
John 
Lucy 
Fred 
Jasmine

如何根据上述名称随机更新NAME字段？

根据评论进行编辑。我将任务数更改为不能被4整除的东西。在这种情况下，现在任务数为6。我想使其无法与其他同事一起完成2个或更多任务。但在这种情况下，某人可以比同事多完成一项任务。他的结果应该类似于（但随机）：

        TASK     |NAME
  1 Get Chicken  |John 
  2 Clean Chicken|Jasmine
  3 Cook Chicken |Lucy 
  4 Eat Chicken  |Fred 
  5 Eat Corn     |Fred
  6 Takeout Trash|Jasmine

Matthew McPe.. 5

这是一种纯SQL方式。

MERGE INTO so_tasks t USING (
WITH numbered_tasks AS ( SELECT t.*, 
                                row_number() OVER (ORDER BY dbms_random.value) task_number, 
                                count(*) OVER () total_tasks FROM so_tasks t ),
     numbered_employees AS ( SELECT e.*, 
                             row_number() OVER (ORDER BY dbms_random.value) employee_number, 
                             count(*) OVER () total_employees FROM so_employees e)
SELECT     nt.task, 
           ne.name
FROM       numbered_tasks nt 
INNER JOIN numbered_employees ne 
      ON ne.employee_number-1 = mod(nt.task_number-1, ne.total_employees) ) u
ON ( t.task = u.task ) 
WHEN MATCHED THEN UPDATE SET t.name = u.name;

它对每个列表进行随机排序，并为每个列表中的每一行分配一个数字。然后从员工列表中获取行，该行的编号与任务编号匹配的MOD员工总数。

这是一个完整的示例：

建立表格

CREATE TABLE so_tasks 
  ( task VARCHAR2(30) NOT NULL PRIMARY KEY,
    name VARCHAR2(30) );

INSERT INTO so_tasks ( task ) VALUES ('Get Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Clean Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Cook Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Eat Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Eat Corn');
INSERT INTO so_tasks ( task ) VALUES ('Takeout Trash');


CREATE TABLE so_employees 
  ( name  VARCHAR2(30) NOT NULL PRIMARY KEY );

INSERT INTO so_employees ( name ) VALUES ('John');
INSERT INTO so_employees ( name ) VALUES ('Lucy');
INSERT INTO so_employees ( name ) VALUES ('Fred');
INSERT INTO so_employees ( name ) VALUES ('Jasmine');

COMMIT;

合并

MERGE INTO so_tasks t USING (
WITH numbered_tasks AS ( SELECT t.*, 
                                row_number() OVER (ORDER BY dbms_random.value) task_number, 
                                count(*) OVER () total_tasks FROM so_tasks t ),
     numbered_employees AS ( SELECT e.*, 
                             row_number() OVER (ORDER BY dbms_random.value) employee_number, 
                             count(*) OVER () total_employees FROM so_employees e)
SELECT     nt.task, 
           ne.name
FROM       numbered_tasks nt 
INNER JOIN numbered_employees ne 
      ON ne.employee_number-1 = mod(nt.task_number-1, ne.total_employees) ) u
ON ( t.task = u.task ) 
WHEN MATCHED THEN UPDATE SET t.name = u.name;

结果

SELECT * FROM so_tasks;

+---------------+---------+
|     TASK      |  NAME   |
+---------------+---------+
| Get Chicken   | John    |
| Clean Chicken | Jasmine |
| Cook Chicken  | Lucy    |
| Eat Chicken   | Fred    |
| Eat Corn      | Jasmine |
| Takeout Trash | Fred    |
+---------------+---------+

您对每项任务的确切分配将有所不同，但任何两名员工之间的任务差异都不会超过一个。

这是一种纯SQL方式。

MERGE INTO so_tasks t USING (
WITH numbered_tasks AS ( SELECT t.*, 
                                row_number() OVER (ORDER BY dbms_random.value) task_number, 
                                count(*) OVER () total_tasks FROM so_tasks t ),
     numbered_employees AS ( SELECT e.*, 
                             row_number() OVER (ORDER BY dbms_random.value) employee_number, 
                             count(*) OVER () total_employees FROM so_employees e)
SELECT     nt.task, 
           ne.name
FROM       numbered_tasks nt 
INNER JOIN numbered_employees ne 
      ON ne.employee_number-1 = mod(nt.task_number-1, ne.total_employees) ) u
ON ( t.task = u.task ) 
WHEN MATCHED THEN UPDATE SET t.name = u.name;

它对每个列表进行随机排序，并为每个列表中的每一行分配一个数字。然后从员工列表中获取行，该行的编号与任务编号匹配的MOD员工总数。

这是一个完整的示例：

建立表格

CREATE TABLE so_tasks 
  ( task VARCHAR2(30) NOT NULL PRIMARY KEY,
    name VARCHAR2(30) );

INSERT INTO so_tasks ( task ) VALUES ('Get Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Clean Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Cook Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Eat Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Eat Corn');
INSERT INTO so_tasks ( task ) VALUES ('Takeout Trash');


CREATE TABLE so_employees 
  ( name  VARCHAR2(30) NOT NULL PRIMARY KEY );

INSERT INTO so_employees ( name ) VALUES ('John');
INSERT INTO so_employees ( name ) VALUES ('Lucy');
INSERT INTO so_employees ( name ) VALUES ('Fred');
INSERT INTO so_employees ( name ) VALUES ('Jasmine');

COMMIT;

合并

MERGE INTO so_tasks t USING (
WITH numbered_tasks AS ( SELECT t.*, 
                                row_number() OVER (ORDER BY dbms_random.value) task_number, 
                                count(*) OVER () total_tasks FROM so_tasks t ),
     numbered_employees AS ( SELECT e.*, 
                             row_number() OVER (ORDER BY dbms_random.value) employee_number, 
                             count(*) OVER () total_employees FROM so_employees e)
SELECT     nt.task, 
           ne.name
FROM       numbered_tasks nt 
INNER JOIN numbered_employees ne 
      ON ne.employee_number-1 = mod(nt.task_number-1, ne.total_employees) ) u
ON ( t.task = u.task ) 
WHEN MATCHED THEN UPDATE SET t.name = u.name;

结果

SELECT * FROM so_tasks;

+---------------+---------+
|     TASK      |  NAME   |
+---------------+---------+
| Get Chicken   | John    |
| Clean Chicken | Jasmine |
| Cook Chicken  | Lucy    |
| Eat Chicken   | Fred    |
| Eat Corn      | Jasmine |
| Takeout Trash | Fred    |
+---------------+---------+

您对每项任务的确切分配将有所不同，但任何两名员工之间的任务差异都不会超过一个。