17,086
社区成员
发帖
与我相关
我的任务
分享
--> --> (jake)生成测试数据
declare n number;
begin
select count(*) into n from user_tables where table_name=upper('T_20081028');
if n>0 then
execute immediate 'drop table T_20081028';
end if;
end;
Create table T_20081028
as
select 1 id ,'q' idname,1 type from dual union all
select 2,'w',1 from dual union all
select 3,'e',1 from dual union all
select 4,'r',1 from dual union all
select 5,'t',1 from dual union all
select 6,'y',2 from dual union all
select 7,'u',2 from dual union all
select 8,'i',2 from dual union all
select 9,'o',2 from dual union all
select 10,'p',2 from dual union all
select 11,'a',3 from dual union all
select 12,'s',3 from dual union all
select 13,'d',3 from dual union all
select 14,'f',3 from dual union all
select 15,'g',3 from dual union all
select 16,'h',3 from dual
;
Select * from T_20081028
select * from
(
select * from T_20081028 where TYPE=1
order by dbms_random.value
)
where rownum <= 3
union all
select * from
(
select * from T_20081028 where TYPE=2
order by dbms_random.value
)
where rownum <= 2
union all
select * from
(
select * from T_20081028 where TYPE=3
order by dbms_random.value
)
where rownum <= 3;
SQL> select * from t;
ID IDNAME TYPE
--- ------------ ----
1 q 1
2 w 1
3 e 1
4 r 1
5 t 1
6 y 2
7 u 2
8 i 2
9 o 2
10 p 2
11 a 3
12 s 3
13 d 3
14 f 3
15 g 3
16 h 3
16 rows selected
SQL>
SQL> select * from (
2 select * from t where type=1 order by dbms_random.value
3 ) where rownum<=3
4 union
5 select * from (
6 select * from t where type=2 order by dbms_random.value
7 ) where rownum<=2
8 union
9 select * from (
10 select * from t where type=3 order by dbms_random.value
11 ) where rownum<=3;
ID IDNAME TYPE
---------- ------------ ----------
1 q 1
2 w 1
5 t 1
6 y 2
8 i 2
13 d 3
14 f 3
16 h 3
8 rows selected
SQL>
SQL> select * from (
2 select * from t where type=1 order by dbms_random.value
3 ) where rownum<=3
4 union
5 select * from (
6 select * from t where type=2 order by dbms_random.value
7 ) where rownum<=2
8 union
9 select * from (
10 select * from t where type=3 order by dbms_random.value
11 ) where rownum<=3;
ID IDNAME TYPE
---------- ------------ ----------
3 e 1
4 r 1
5 t 1
7 u 2
10 p 2
11 a 3
14 f 3
15 g 3
8 rows selected
SQL>