表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次分别取出来， 这样很是浪费时间！！
------解决方案--------------------
- SQL code
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>
------解决方案--------------------
- SQL code
--> --> (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_20081028as select 1 id ,'q' idname,1 type from dual union allselect 2,'w',1 from dual union allselect 3,'e',1 from dual union allselect 4,'r',1 from dual union allselect 5,'t',1 from dual union allselect 6,'y',2 from dual union allselect 7,'u',2 from dual union allselect 8,'i',2 from dual union allselect 9,'o',2 from dual union allselect 10,'p',2 from dual union allselect 11,'a',3 from dual union allselect 12,'s',3 from dual union allselect 13,'d',3 from dual union allselect 14,'f',3 from dual union allselect 15,'g',3 from dual union allselect 16,'h',3 from dual;Select * from T_20081028select * from( select * from T_20081028 where TYPE=1order by dbms_random.value)where rownum <= 3union allselect * from( select * from T_20081028 where TYPE=2order by dbms_random.value)where rownum <= 2union allselect * from( select * from T_20081028 where TYPE=3order by dbms_random.value)where rownum <= 3;