select * from (select * from tb_name order by dbms_random.value ) where rownum <= N
xiaoxiao1984(笨猫儿^_^) select * from t1 sample(N) ;--随机取得大概N条记录 是什么意思?我怎么没试成功呢?
--随机取得百分之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(*) ---------- 749852SQL> select count(*) from aa sample(20); COUNT(*) ---------- 299869
有个问题: 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 感觉效果一样啊?
不一样,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条记录
SQL> select * from tab_1 order by dbms_random.value; ID NAME ---------- ---------- 2 a 3 b 1 a 4 b 158 x 152 x6 rows selectedSQL> select * from tab_1 order by dbms_random.value; ID NAME ---------- ---------- 2 a 4 b 152 x 1 a 3 b 158 x6 rows selectedSQL> select * from tab_1 order by dbms_random.value; ID NAME ---------- ---------- 4 b 2 a 158 x 152 x 1 a 3 b6 rows selectedSQL> select * from tab_1 order by dbms_random.value; ID NAME ---------- ---------- 3 b 2 a 158 x 4 b 1 a 152 x6 rows selected楼主确定拼写正确么
select * from t1 sample(N) ;--随机取得大概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(*)
----------
749852SQL> select count(*) from aa sample(20); COUNT(*)
----------
299869
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
感觉效果一样啊?
---------- ----------
2 a
3 b
1 a
4 b
158 x
152 x6 rows selectedSQL> select * from tab_1 order by dbms_random.value; ID NAME
---------- ----------
2 a
4 b
152 x
1 a
3 b
158 x6 rows selectedSQL> select * from tab_1 order by dbms_random.value; ID NAME
---------- ----------
4 b
2 a
158 x
152 x
1 a
3 b6 rows selectedSQL> select * from tab_1 order by dbms_random.value; ID NAME
---------- ----------
3 b
2 a
158 x
4 b
1 a
152 x6 rows selected楼主确定拼写正确么