根据oracle中的计数在oracle中生成随机数(非重复整数)

u91tlkcl 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(183)

看看我是否可以根据总计数生成一个非重复随机整数-例如下面的代码-

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.*,
(DBMS_RANDOM.VALUE( 0, X?? )) as RND from (
select B.* from all_val B
where VAL <> 'B') A;

ID的1-3和7-9将被选中，但生成的随机数只能是1-6。
希望这有意义。
Tia

oracle

来源：https://stackoverflow.com/questions/76908989/generate-random-number-non-repeating-integer-in-oracle-based-on-the-count-in-o

1条答案

按热度按时间

blmhpbnm1#

如果你想让随机数的范围从1到查询返回的行数，你可以通过传递count(*) over ()作为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.*,
  round ( dbms_random.value ( 1, count (*) over () ) ) as rnd
from(
  select b.*
  from   all_val b
  where  val <> 'B'
) a;

ID    VAL    RND    
    1 A           1 
    2 A           2 
    3 A           6 
    7 A           5 
    8 A           6 
    9 A           3

赞(0）回复(0）举报 2023-10-16

我来回答

根据oracle中的计数在oracle中生成随机数(非重复整数)

1条答案

相关问题

热门标签

最新问答