Oracle中基于筛选记录数生成不重复随机整数的技术咨询

要实现为筛选后的记录生成指定范围的不重复随机整数，不能直接使用DBMS_RANDOM.VALUE()（该函数会产生重复值），正确的做法是先对筛选结果做随机排序，再通过行号函数生成连续不重复的整数。

实现代码 #

with all_val as (    
select 1 as ID, 'A' as val from dual  union all 
select 2 as ID, 'A' as val from dual  union all 
select 3 as ID, 'A' as val from dual  union all  
select 4 as ID, 'B' as val from dual  union all 
select 5 as ID, 'B' as val from dual  union all  
select 6 as ID, 'B' as val from dual  union all 
select 7 as ID, 'A' as val from dual  union all 
select 8 as ID, 'A' as val from dual  union all 
select 9 as ID, 'A' as val from dual)
select A.*,
       ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE()) as RND
from (
    select B.* from all_val B
    where VAL <> 'B'
) A;

原理说明 #

1. 子查询A先筛选出VAL <> 'B'的6条记录；
2. 通过ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE())，以随机生成的值为排序依据，对筛选结果进行随机打乱；
3. ROW_NUMBER()会为打乱后的每条记录分配一个从1开始的连续整数，最终得到1-6范围内的不重复随机数。

如果需要生成0-5范围的不重复随机数，只需将行号结果减1：

ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE()) - 1 as RND

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