随机取表中数据

在pl/sql中有张1000条记录的表。如果想随机抽取其中50条记录该如何操作呢？[@more@]

利用select *from (select *from t order by dbms_random.value) where rownum<10;

for example:

SQL> select count(*) from all_objects;

COUNT(*)
----------
23910


SQL> create table seaman_t as select * from all_objects where rownum <=2000;

Table created.


SQL> select object_name, object_id from ( select object_name, object_id from seaman_t where object_name like '%%' order by dbms_random.random ) where rownum <= 4;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
GV_$LOADPSTAT 1858
USER_CONS_COLUMNS 1363
DBMS_AQ_SYS_EXP_INTERNAL 2455
EXU8CSNU 1620

SQL> select object_name, object_id from ( select object_name, object_id from seaman_t where object_name like '%%' order by dbms_random.random ) where rownum <= 4;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
ALL_TAB_COL_STATISTICS 1300
USER_SUMMARY_AGGREGATES 2057
oracle/ODCI/ODCIPredInfoRef 7101312
EXU81CSC 1846

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/119395/viewspace-908196/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/119395/viewspace-908196/