表A 有如下数据: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从中随机选择3个TYPE 为1 ,2个TYPE为2,3个TYPE为3 的数据,
结果类似如下所示:
2 w 1
3 e 1
4 r 1
9 o 2
7 u 2
13 d 3
14 f 3
15 g 3请大家赐教,如何在一条SQL 语句中实现,我循环了3次分别取出来, 这样很是浪费时间!!
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从中随机选择3个TYPE 为1 ,2个TYPE为2,3个TYPE为3 的数据,
结果类似如下所示:
2 w 1
3 e 1
4 r 1
9 o 2
7 u 2
13 d 3
14 f 3
15 g 3请大家赐教,如何在一条SQL 语句中实现,我循环了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 316 rows selectedSQL>
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 38 rows selectedSQL>
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 38 rows selectedSQL>
--> --> (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_20081028select * 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;