关于Oracle记录随机排序

Question

本帖最后由 antsleg 于 2010-08-18 20:40:46 编辑

Answer 1

这种是把DBMS_RANDOM.value当成一列来处理了。相当于
SELECT t.*,DBMS_RANDOM.value FROM TableName1 t 
ORDER BY DBMS_RANDOM.value;

Answer 2

order by 指定列的时候参数仅限于介于列数之间的正整数。order by后面跟变量时，是按字段解析的。

Answer 3

ORDER BY 1;   这个是根据第一列的值来排序的 固定的 一直是1
ORDER BY DBMS_RANDOM.value; DBMS_RANDOM.value 这本来就是个随机的值 怎么能跟列来比
DBMS_RANDOM.value 的值就不是固定的有可能是1或者其他的
 

Answer 4

是啊，这种排的话，数据有n行，你会得到n!中排序
你可以对n=3，和n=2做做验证

Answer 5

 select * from emp
 order by trunc(dbms_random.value(1,8));
--dbms_random.value(1,8)是取1-8之间的随机数 有小数
--trunc(dbms_random.value(1,8))截取整数部分

Answer 6

SQL> SELECT t.* FROM test t WHERE ROWNUM<=3
  2  order BY decode(ID,NULL,1,1) ASC;
 
                   ID TYPE                                                                             VALUE
--------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
                  874 874                                                                              874
                  875 875                                                                              875
                  876 876                                                                              876
 
SQL> 
SQL> SELECT t.* FROM test t WHERE ROWNUM<=3
  2  order BY decode(ID,NULL,1,1) DESC;
 
                   ID TYPE                                                                             VALUE
--------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
                  874 874                                                                              874
                  875 875                                                                              875
                  876 876                                                                              876
 
SQL> 
SQL> SELECT t.* FROM test t WHERE ROWNUM<=3
  2  order BY  1 DESC;
 
                   ID TYPE                                                                             VALUE
--------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
                  876 876                                                                              876
                  875 875                                                                              875
                  874 874                                                                              874
 
SQL> 
SQL> SELECT t.* FROM test t WHERE ROWNUM<=3
  2  order BY  1 ASC;
 
                   ID TYPE                                                                             VALUE
--------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
                  874 874                                                                              874
                  875 875                                                                              875
                  876 876                                                                              876
 
SQL> 

Answer 7

感觉order by 1之类的就是oracle的一种土写法。别打我。。