【SQL】使用SQL语句完成20个护士随机分配到两个医院的任务

今天被问到有关如果将20个人随机的分配到两个组的问题，现将实现方法分享在此，供参考。

1.问题需求
有20个护士，需要随机取出10位护士分配到A医院，剩下的10位护士分配到B医院。
为了单位的分配公平，必须是随机生成这个结果。

2.问题解析
该问题转换为最浅显的解释就是，对给出的1-20连续的数字，任意打乱，将打乱的结果排成一列。前10人就去A医院，后10人就去B医院。

3.模型化这个问题
create table name_list (name varchar2(30));
insert into name_list values ('secooler1');
insert into name_list values ('secooler2');
insert into name_list values ('secooler3');
insert into name_list values ('secooler4');
insert into name_list values ('secooler5');
insert into name_list values ('secooler6');
insert into name_list values ('secooler7');
insert into name_list values ('secooler8');
insert into name_list values ('secooler9');
insert into name_list values ('secooler10');
insert into name_list values ('secooler11');
insert into name_list values ('secooler12');
insert into name_list values ('secooler13');
insert into name_list values ('secooler14');
insert into name_list values ('secooler15');
insert into name_list values ('secooler16');
insert into name_list values ('secooler17');
insert into name_list values ('secooler18');
insert into name_list values ('secooler19');
insert into name_list values ('secooler20');
commit;

sec@ora10g> select * from name_list;

NAME
------------------------------
secooler1
secooler2
secooler3
secooler4
secooler5
secooler6
secooler7
secooler8
secooler9
secooler10
secooler11
secooler12
secooler13
secooler14
secooler15
secooler16
secooler17
secooler18
secooler19
secooler20

20 rows selected.

4.方案一
使用Oracle的随机函数实现这个需求最简单直观。
方法如下：
在将name_list表打乱之前的内容如下：
sec@ora10g> select rownum, t.* from (select * from name_list) t;

    ROWNUM NAME
---------- ------------------------------
         1 secooler1
         2 secooler2
         3 secooler3
         4 secooler4
         5 secooler5
         6 secooler6
         7 secooler7
         8 secooler8
         9 secooler9
        10 secooler10
        11 secooler11
        12 secooler12
        13 secooler13
        14 secooler14
        15 secooler15
        16 secooler16
        17 secooler17
        18 secooler18
        19 secooler19
        20 secooler20

20 rows selected.

我们使用随机数dbms_random.random对name_list表进行打乱后输出。
sec@ora10g> select rownum, t.* from (select * from name_list order by dbms_random.random) t;

    ROWNUM NAME
---------- ------------------------------
         1 secooler10
         2 secooler9
         3 secooler19
         4 secooler7
         5 secooler3
         6 secooler4
         7 secooler15
         8 secooler16
         9 secooler18
        10 secooler12
        11 secooler1
        12 secooler5
        13 secooler13
        14 secooler14
        15 secooler6
        16 secooler17
        17 secooler2
        18 secooler11
        19 secooler8
        20 secooler20

20 rows selected.

每次执行这个条语句都可以完成一次打乱的需求，自行测试，不赘述。

原理：对非常大的随机数进行排序以达到顺序打乱的目的。

5.方案二
基于第一种方法，只要我们在name_list表中添加一列随机数列，然后再按照这一列进行排序即可。
sec@ora10g> alter table name_list add random number;

Table altered.

sec@ora10g> update name_list set random=dbms_random.random;

20 rows updated.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from name_list;

NAME                               RANDOM
------------------------------ ----------
secooler1                      -1.645E+09
secooler2                      1441099457
secooler3                      -1.133E+09
secooler4                       600418771
secooler5                      1277685516
secooler6                      -177083719
secooler7                      -599498736
secooler8                       352353250
secooler9                       107600398
secooler10                      718837634
secooler11                     -830730584
secooler12                      906134037
secooler13                     -1.082E+09
secooler14                       11469174
secooler15                     -452139368
secooler16                     -2.119E+09
secooler17                      760306608
secooler18                      158227683
secooler19                       21379248
secooler20                     1617624650

20 rows selected.

sec@ora10g> select rownum, t.* from (select name from name_list order by random) t;

    ROWNUM NAME
---------- ------------------------------
         1 secooler16
         2 secooler1
         3 secooler3
         4 secooler13
         5 secooler11
         6 secooler7
         7 secooler15
         8 secooler6
         9 secooler14
        10 secooler19
        11 secooler9
        12 secooler18
        13 secooler8
        14 secooler4
        15 secooler10
        16 secooler17
        17 secooler12
        18 secooler5
        19 secooler2
        20 secooler20

20 rows selected.

OK，这样，我们就完成了一次数据打乱的动作。

6.方案三
基于上面添加辅助字段的方法，我们可以大胆的将这种方法推广到Excel中。
我们可以使用Excel的“=rand()”方法生成随机数，然后按照这个随机数列进行排序及可完成数据随机打乱功能。
在此基础上可以录制一段“宏”，使得这个过程可以自动化完成，这样每次执行宏都会获得一个新的随机排列，甚是方便。
这个方案任何人都可以完成，毕竟Office软件还是很普及的。

7.方案四
这里只给出思路，不做具体实现。
思路：构造另外一个表T，从表name_list中随机取出一条记录插入到表T中，同时将name_list中的该条记录删除；再从表name_list中随机取出一条记录插入到表T中，同时将name_list中的该条记录删除，按照这个方法循环下去，直到所有数据均插入到T表中，此时T表就是一个打乱后的结果。

8.方案五
如果想用“牛刀”完成这个需求，也可以通过编写一段小程序或小脚本来完成（注意在编写过程中注意选取的内容不可以重复）。

9.小结
往往一个需求拿到手中后，解决方案不止一个，这时，我们就需要认真的甄别那种方案的代价最低。关键是快速的转换问题需求为我们熟识技术点。
本例中使用SQL的随机函数排序方法最简单，使用Excel方法最实用，使用程序或脚本方法较复杂，各取所需最重要。

Good luck.

secooler
10.04.28

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-661592/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-661592/