SQL Cookbook:一、检索记录(10)从表中返回随机的n条记录
问题
从表中随机返回n条记录。可以修改下面的语句,要求下次执行时产生不同的结果集。
select ename, job from emp
解决方案
使用DBMS支持的内置函数来生成随机数值。在ORDEY BY子句中使用该函数,对行进行随机排序,然后,使用前面的问题介绍的技巧,来限制所返回的顺序随机的行的数目。
DB2
使用内置函数RAND搭配使用ORDER BY和FETCH:
select ename,job from emp order by rand() fetch first 5 rows only
MySQL
使用内置函数RAND搭配使用LIMIT和ORDER BY:
select ename,job from emp order by rand() limit 5
PostgreSQL
使用内置函数RAND搭配使用LIMIT和ORDER BY:
select ename,job from emp order by random() limit 5
Oracle
使用DBMS_RANDOM包中的内置函数VALUE,搭配使用ORDER BY和内置函数ROWNUM:
select * from ( select ename, job from emp order by dbms_random.value() ) where rownum <= 5
SQL Server
使用内置函数NEWID搭配使用TOP和ORDER BY返回随机结果集:
select top 5 ename,job from emp order by newid()
讨论
ORDER BY子句可以接受函数的返回值,并使用它来改变结果集的次序。该解决方案中,在ORDER BY子句中执行函数之后,再查询返回的行数。非Oracle用户会发现看看Oracle解决方案会很有用,可以理解解决方案的原理。
重要的是,不要把在ORDER BY子句中使用函数和使用数值常量混合起来。在ORDER BY子句中指定数值常量时,是要求根据SELECT列表中相应位置的列来排序,当在ORDER BY子句中使用函数时则按照每一行的计算结果来排序。