如何能随机抽取数据？

piner

最初由 shahand 发布
[B]

我的9204没有问题

:wq

  1* select employee_id,last_name from (select * from employee order by sys_guid()) where rownum<4
SQL> r
  1* select employee_id,last_name from (select * from employee order by sys_guid()) where rownum<4

EMPLOYEE_ID LAST_NAME
----------- ---------------
       7505 DOYLE
       7950 JENSEN
       7902 FORD

SQL> r
  1* select employee_id,last_name from (select * from employee order by sys_guid()) where rownum<4

EMPLOYEE_ID LAST_NAME
----------- ---------------
       7506 DENNIS
       7369 SMITH
       7789 WEST

SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production [/B]

应当不是所有情况，在有些情况下，sys_guid()出来的顺序总是固定的。

[php]
SQL> select empno from (select * from emp t order by sys_guid()) where rownum <4;

EMPNO
-----
7369
7499
7521

SQL> /

EMPNO
-----
7369
7499
7521

SQL> /

EMPNO
-----
7369
7499
7521


SQL> select empno,sid from (select sys_guid() sid,t.* from emp t order by sys_guid()) where rownum <4;

EMPNO SID
----- --------------------------------
7369 EC450EF27ED85180E030000000001C8A
7499 EC450EF27ED95180E030000000001C8A
7521 EC450EF27EDA5180E030000000001C8A

SQL> /

EMPNO SID
----- --------------------------------
7369 EC450EF27EE45180E030000000001C8A
7499 EC450EF27EE55180E030000000001C8A
7521 EC450EF27EE65180E030000000001C8A

SQL> /

EMPNO SID
----- --------------------------------
7369 EC450EF27EF05180E030000000001C8A
7499 EC450EF27EF15180E030000000001C8A
7521 EC450EF27EF25180E030000000001C8A



SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE        9.2.0.3.0        Production

TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
[/php]

vcpubljj

select * from test order by dbms_random.random solution mine,thanks