最好的办法是使用row_number加dbms_random实现,例子如下:SELECT OBJECT_NAME FROM (SELECT OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE) RN FROM USER_OBJECTS S) O WHERE RN <= 10;
试了一下 rownum<=10 貌似只取前10行,不是随机的吧?~
try it: select * from (select * from A order by dbms_random.value) where rownum <= 10
select * from (select a,b,c,row_number()over(order by a)rn from A) where rn<=10
select * from (select distinct * from A order by dbms_random.value) where rownum <= 10
借楼主宝地,to hyrongg(无名老卒):你太客气了,相互学习,你的车展摄影不错哟。 :-)
防止重复的唯一办法是把查询过的放到临时表中,下次查询先minus上次查询的,再用上面的方法,类似如下:你的业务流程就变成查询创建的临时表tt2: CREATE TABLE tt1 AS SELECT object_name FROM user_objects WHERE 1=2; CREATE TABLE tt2 AS SELECT object_name FROM user_objects WHERE 1=2; DELETE FROM tt2; INSERT INTO tt2 SELECT OBJECT_NAME FROM (SELECT OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE) RN FROM (SELECT OBJECT_NAME FROM USER_OBJECTS MINUS SELECT OBJECT_NAME FROM TT1)) O WHERE RN <= 10; INSERT INTO tt1 SELECT * FROM tt2; SELECT * FROM tt2;
select * from A where rownum<=10;
FROM (SELECT OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE) RN
FROM USER_OBJECTS S) O
WHERE RN <= 10;
select *
from (select * from A order by dbms_random.value)
where rownum <= 10
*
from
(select a,b,c,row_number()over(order by a)rn from A)
where
rn<=10
好强啊~
那如果有重复的怎么办?~ 会有概率出现重复的吧?~
怎么能防止会有重复的OBJECT_NAME
CREATE TABLE tt1 AS SELECT object_name FROM user_objects WHERE 1=2;
CREATE TABLE tt2 AS SELECT object_name FROM user_objects WHERE 1=2;
DELETE FROM tt2;
INSERT INTO tt2
SELECT OBJECT_NAME
FROM (SELECT OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.VALUE) RN
FROM (SELECT OBJECT_NAME
FROM USER_OBJECTS
MINUS
SELECT OBJECT_NAME FROM TT1)) O
WHERE RN <= 10;
INSERT INTO tt1 SELECT * FROM tt2;
SELECT * FROM tt2;