如何随机从一个表中取数据呢？

[marcovanbasten]

我用的是ORACLE9i，现在想从一个表中，随机地取出N条（或是百分之几）的记录，请问如何办到啊？急！

[xiaoxiao1984]

select * from t1 sample(N) ;--随机取得大概N条记录

[xiaoxiao1984]

select * from (select * from tb_name order by dbms_random.value ) where rownum <= N

[swimming8243]

mark

[guangli_zhang]

xiaoxiao1984(笨猫儿^_^)
select * from t1 sample(N) ;--随机取得大概N条记录
是什么意思？我怎么没试成功呢？

[碧海情天-赵亮]

不错,收藏了

[xiaoxiao1984]

--随机取得百分之N的纪录
select * from t1 sample(N) ;

--随机取得大概N条记录
select * from (select * from tb_name order by dbms_random.value ) where rownum <= N;

SQL> select count(*) from aa sample(50);

COUNT(*)
----------
749852

SQL> select count(*) from aa sample(20);

COUNT(*)
----------
299869

[marcovanbasten]

有个问题：
select * from (select * from tb_name order by dbms_random.value ) where rownum <= N;
上面这句为什么不能写成：
select * from tb_name where rownum <= N order by dbms_random.value
感觉效果一样啊？

[xiaoxiao1984]

不一样，select * from tb_name where rownum <= N order by dbms_random.value 先取到固定的N条记录然后随机排序

select * from (select * from tb_name order by dbms_random.value ) where rownum <= N; 先随机排序，然后取得前N条记录

[marcovanbasten]

楼上的有道理。可是我在实际操作的时候，发现得到的结果好像还挺象的。

[xiaoxiao1984]

SQL> select * from tab_1 order by dbms_random.value;

ID NAME
---------- ----------
2 a
3 b
1 a
4 b
158 x
152 x

6 rows selected

SQL> select * from tab_1 order by dbms_random.value;

ID NAME
---------- ----------
2 a
4 b
152 x
1 a
3 b
158 x

6 rows selected

SQL> select * from tab_1 order by dbms_random.value;

ID NAME
---------- ----------
4 b
2 a
158 x
152 x
1 a
3 b

6 rows selected

SQL> select * from tab_1 order by dbms_random.value;

ID NAME
---------- ----------
3 b
2 a
158 x
4 b
1 a
152 x

6 rows selected

楼主确定拼写正确么

[marcovanbasten]

啥也不说了。给分！