生成随机数目的子记录

mmvthczy 于 2021-07-26 发布在 Java

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

我成功地使用下面的代码生成了20个父记录，每个记录有15个子记录。如何修改此代码以为每个父级生成随机数（即5-20）随机子记录。
创建表emp_info（empid integer，empname varchar2（50））；

CREATE TABLE  emp_attendance    
(empid INTEGER,
 start_date DATE,
 end_date DATE
 );

--带cte的选件

insert all 
  when rn = 1 then into emp_info   (empid, empname) values (id, name)
  when  1 = 1 then into emp_attendance (empid, start_date, end_date) 
               values (id, d1, d1 + dbms_random.value (0, .75))
with t as (select nvl(max(empid), 0)     maxid from emp_info)
select ceil(maxid + level/15) id,
       case mod(maxid + level, 15) when 1 then 1 end rn, 
       dbms_random.string('U',     dbms_random.value(3, 15)) name, 
       trunc(sysdate) +    dbms_random.value (1, 30) d1
  from t connect by level <= 20 * 15;
-- 20 parent records 15 children each

sql oracle parent-child

来源：https://stackoverflow.com/questions/62192183/generating-a-random-number-of-child-records

1条答案

按热度按时间

4ktjp1zp1#

你可以利用 ROW_NUMBER 功能如下：
--有关说明，请参见内联注解

insert all 
  when rn = 1 then into emp_info   (empid, empname) values (id, name)
  when  1 = 1 then into emp_attendance (empid, start_date, end_date) 
               values (id, d1, d1 + dbms_random.value (0, .75))
select * from 
(
with t as (select nvl(max(empid), 0)     maxid from emp_info)
select ceil(maxid + level/15) id,
       case mod(maxid + level, 15) when 1 then 1 end rn, 
       dbms_random.string('U',     dbms_random.value(3, 15)) name, 
       trunc(sysdate) +    dbms_random.value (1, 30) d1,
       case when row_number() over (partition by ceil(maxid + level/15) 
                                        order by level) > 5 then 
       dbms_random.value(5, 20)
       else 5 end as random_val -- added this expression as column
  from t connect by level <= 20 * 20 -- changed it from 15 to 20
)
  where random_val <= 12; -- this is random number less than 20

赞(0）回复(0）举报 2021-07-26

我来回答

生成随机数目的子记录

1条答案

相关问题

热门标签

最新问答