oracle sqlserver mysql随机行

oracle随机行的实现

SQL> select * from (select * from scott.emp order by dbms_random.value) where rownum < 11;                                                                                     

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 1981/04/02       2975                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
      7369 SMITH      CLERK           7902 1980/12/17        800                    20
      7839 KING       PRESIDENT            1981/11/17       5000                    10
      7698 BLAKE      MANAGER         7839 1981/05/01       2850                    30
      7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
      7876 ADAMS      CLERK           7788 1987/05/23       1100                    20
      7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
      7900 JAMES      CLERK           7698 1981/12/03        950                    30
      7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30

10 rows selected.

SQL> select * from (select * from scott.emp order by dbms_random.value) where rownum < 11;                                                                                     

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 1981/12/03        950                    30
      7369 SMITH      CLERK           7902 1980/12/17        800                    20
      7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30
      7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
      7902 FORD       ANALYST         7566 1981/12/03       3000                    20
      7839 KING       PRESIDENT            1981/11/17       5000                    10
      7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
      7782 CLARK      MANAGER         7839 1981/06/09       2450                    10
      7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
      7566 JONES      MANAGER         7839 1981/04/02       2975                    20

10 rows selected.

SQL>

 

---------------------------------------------------------------------------------------------------------------------

sqlserver随机行的实现

select top 10 * from test03 order by newid()

3833 c
70686 c
64770 c
7025 c
63327 c
61120 c
44593 c
89396 c
94987 c
25273 c

select top 10 * from test03 order by newid()

101269 c
77968 c
90425 c
21525 c
49701 c
31501 c
109021 c
52655 c
40267 c
74737 c

 

----------------------------------------------------------------------------

mysql随机行实现

mysql> select link_id,link_name from wp_links  order by rand() limit 4;
+---------+------------------+
| link_id | link_name        |
+---------+------------------+
|       2 | WordPress Blog   |
|       1 | Documentation    |
|       7 | WordPress Planet |
|       6 | Themes           |
+---------+------------------+
4 rows in set (0.00 sec)

mysql> select link_id,link_name from wp_links  order by rand() limit 4;
+---------+----------------+
| link_id | link_name      |
+---------+----------------+
|       4 | Support Forum  |
|       2 | WordPress Blog |
|       5 | Plugins        |
|       6 | Themes         |
+---------+----------------+
4 rows in set (0.00 sec)