很多人以为,记录是按照插入的时间顺序返回的,比如:
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1040 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已选择14行。
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1040 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已选择14行。
SQL>
其实我们可以多次运行这个语句,你会发现,返回记录的顺序都是一致的。
是按照插入时间顺序返回的吗?或者是按照某种固定条件返回的吗?不是!
根据Oracle的官方文档描述,数据库并不保证每次select的结果顺序都一致,不过我们看到的好像都是一致的。
如果要保证按照某种顺序返回,那么必须使用“order by 表字段”来额外排序。
那么返回记录是按照什么来排序的呢?答案是rownum伪列!
下面来验证:
SQL> select rownum,empno,ename from emp;
ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
已选择14行。
SQL> select rownum,empno,ename from emp;
ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
已选择14行。
SQL>
不管你运行多少次,记录一定是按照rownum的顺序asc排列的。
然而,我们又知道rownum其实并不是表里的某个字段,而是数据库在提取到数据之后,给每条记录打上的一个“行标签”(可以这么理解的)。
由于整个行标签是提取到数据行之后,再计算的,所以我们可以认为数据库其实是随机返回的行记录。
我们可以使用随机函数来打破这种排序:
SQL> select rownum,empno,ename from emp order by dbms_random.value;
ROWNUM EMPNO ENAME
---------- ---------- ----------
8 7788 SCOTT
6 7698 BLAKE
4 7566 JONES
13 7902 FORD
7 7782 CLARK
3 7521 WARD
2 7499 ALLEN
14 7934 MILLER
10 7844 TURNER
1 7369 SMITH
9 7839 KING
12 7900 JAMES
11 7876 ADAMS
5 7654 MARTIN
已选择14行。
SQL> select rownum,empno,ename from emp order by dbms_random.value;
ROWNUM EMPNO ENAME
---------- ---------- ----------
2 7499 ALLEN
1 7369 SMITH
9 7839 KING
6 7698 BLAKE
13 7902 FORD
10 7844 TURNER
3 7521 WARD
11 7876 ADAMS
12 7900 JAMES
5 7654 MARTIN
14 7934 MILLER
8 7788 SCOTT
7 7782 CLARK
4 7566 JONES
已选择14行。
SQL>
很明显,返回数据不是按照rownum来排序了,而是按照dbms_random.value生成的随机数来排序的。
其实上面是利用方法dbms_random.value,返回一个随机数,然后按照这个随机数列进行asc排序,从而实现表记录随机排序的:
SQL> select a.*,dbms_random.value() from emp a order by dbms_random.value;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DBMS_RANDOM.VALUE()
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------------
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 .034429293
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 .07152092
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 .15250888
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 .26048267
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 .2978804
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 .558679091
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 .627771484
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 .632164113
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 .679326656
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 .706778721
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 .883335547
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 .902285485
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 .933018413
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1040 20 .993865558
已选择14行。
SQL> select a.*,dbms_random.value() from emp a order by dbms_random.value;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DBMS_RANDOM.VALUE()
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 .100835201
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 .1164041
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 .127811743
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 .128593597
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 .219292846
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 .268933658
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1040 20 .329608481
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 .403190065
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 .435527681
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 .478610064
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 .495648474
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 .561227421
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 .788137149
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 .848859867
已选择14行。
SQL>
可以单独运行dbms_random.value()多次,看到的结果是每次返回值都不一样:
SQL> select dbms_random.value() from dual;
DBMS_RANDOM.VALUE()
-------------------
.683194105
SQL> select dbms_random.value() from dual;
DBMS_RANDOM.VALUE()
-------------------
.899251721
SQL> select dbms_random.value() from dual;
DBMS_RANDOM.VALUE()
-------------------
.763571008
SQL> select dbms_random.value() from dual;
DBMS_RANDOM.VALUE()
-------------------
.337395486
SQL>
所以,比如论坛的帖子,我们要随机显示10个主题,那么该怎么写这样的sql?
还是以emp表为例:
SQL> select * from (select empno,ename from emp order by dbms_random.value) where rownum<=10;
EMPNO ENAME
---------- ----------
7566 JONES
7788 SCOTT
7902 FORD
7934 MILLER
7900 JAMES
7499 ALLEN
7839 KING
7782 CLARK
7521 WARD
7844 TURNER
已选择10行。
SQL> select * from (select empno,ename from emp order by dbms_random.value) where rownum<=10;
EMPNO ENAME
---------- ----------
7521 WARD
7369 SMITH
7788 SCOTT
7902 FORD
7654 MARTIN
7566 JONES
7876 ADAMS
7782 CLARK
7934 MILLER
7839 KING
已选择10行。
SQL>
我们应该是要将所有的帖子以“随机排序”的方式返回,然后再使用rownum来提取前10个主题即可。