SQL RAND 函數(shù)

SQL 有一個(gè) RAND 函數(shù)，用于產(chǎn)生 0 至 1 之間的隨機(jī)數(shù)：

    SQL>  SELECT RAND( ), RAND( ), RAND( );
    +------------------+-----------------+------------------+
    | RAND( )          | RAND( )         | RAND( )          |
    +------------------+-----------------+------------------+
    | 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |
    +------------------+-----------------+------------------+
    1 row in set (0.00 sec)

當(dāng)以某個(gè)整數(shù)值作為參數(shù)來調(diào)用的時(shí)候，RAND() 會將該值作為隨機(jī)數(shù)發(fā)生器的種子。對于每一個(gè)給定的種子，RAND() 函數(shù)都會產(chǎn)生一列可以復(fù)現(xiàn)的數(shù)字：

    SQL>  SELECT RAND(1), RAND( ), RAND( );
    +------------------+------------------+------------------+
    | RAND(1 )         | RAND( )          | RAND( )          |
    +------------------+------------------+------------------+
    | 0.18109050223705 | 0.75023211143001 | 0.20788908117254 |
    +------------------+------------------+------------------+
    1 row in set (0.00 sec)

你可以使用 ORDER BY RAND() 來對一組記錄進(jìn)行隨機(jī)化排列，如下所示：

    SQL> SELECT * FROM employee_tbl;
    +------+------+------------+--------------------+
    | id   | name | work_date  | daily_typing_pages |
    +------+------+------------+--------------------+
    |    1 | John | 2007-01-24 |                250 |
    |    2 | Ram  | 2007-05-27 |                220 |
    |    3 | Jack | 2007-05-06 |                170 |
    |    3 | Jack | 2007-04-06 |                100 |
    |    4 | Jill | 2007-04-06 |                220 |
    |    5 | Zara | 2007-06-06 |                300 |
    |    5 | Zara | 2007-02-06 |                350 |
    +------+------+------------+--------------------+
    7 rows in set (0.00 sec)

現(xiàn)在，試試下面的命令：

    SQL> SELECT * FROM employee_tbl ORDER BY RAND();
    +------+------+------------+--------------------+
    | id   | name | work_date  | daily_typing_pages |
    +------+------+------------+--------------------+
    |    5 | Zara | 2007-06-06 |                300 |
    |    3 | Jack | 2007-04-06 |                100 |
    |    3 | Jack | 2007-05-06 |                170 |
    |    2 | Ram  | 2007-05-27 |                220 |
    |    4 | Jill | 2007-04-06 |                220 |
    |    5 | Zara | 2007-02-06 |                350 |
    |    1 | John | 2007-01-24 |                250 |
    +------+------+------------+--------------------+
    7 rows in set (0.01 sec)

    SQL> SELECT * FROM employee_tbl ORDER BY RAND();
    +------+------+------------+--------------------+
    | id   | name | work_date  | daily_typing_pages |
    +------+------+------------+--------------------+
    |    5 | Zara | 2007-02-06 |                350 |
    |    2 | Ram  | 2007-05-27 |                220 |
    |    3 | Jack | 2007-04-06 |                100 |
    |    1 | John | 2007-01-24 |                250 |
    |    4 | Jill | 2007-04-06 |                220 |
    |    3 | Jack | 2007-05-06 |                170 |
    |    5 | Zara | 2007-06-06 |                300 |
    +------+------+------------+--------------------+
    7 rows in set (0.00 sec)