随机取舍数据

随机选择数据
表A 有如下数据：

IDIDNAMETYPE
1q1
2w1
3e1
4r1
5t1
6y2
7u2
8i2
9o2
10p2
11a3
12s3
13d3
14f3
15g3
16h3

从中随机选择3个TYPE 为1 ，2个TYPE为2，3个TYPE为3 的数据，
结果类似如下所示：
2w1
3e1
4r1
9o2
7u2
13d3
14f3
15g3

请大家赐教，如何在一条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;